Excel spreadsheets are one of the most useful tools to create documents with calculations and data and organize the accounting or VAT tax, among other tasks. Luckily, while working on Excel you can use various formulas to speed up and facilitate the process. In this article, we will show you what it is and how to use formulas.
The primary use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of a cell. A formula is used to represent an expression, which, one entered into the cell, allows the spreadsheet to perform automatic calculations based on values in other cells and to display the result. Formulas may also contain references to other cells, expressions, or functions.
How to enter a formula?
- Entering a formula is done by selecting a cell and by using the formula bar.
- To enter a formula, start by entering the equal sign (=) and then enter the cell references of the cells to be used and the operators and/or functions.
- It is also possible to click Formula on the formula bar button. The example below demonstrates a formula in cell C15 to calculate the sum of cells D11 and D13.
- When the Enter key is pushed, the spreadsheet calculates the value of cell C15 and displays the result:
- In the example below, the use of formulas to automatically calculate the price with tax included (Tax included) of a list of articles for which the "Before Tax" (BT) price is given, and then to calculate the total price of these articles is shown.
- To obtain the Tax Included price for article 34FR453, add the value of the tax to the pre-tax value, meaning the pre-tax value multiplied by the VAT (19.6% in this case):
Amount_TI = Amount_BT + Amount_BT*VAT
In the above example,
- the TI amount is located in E6,
- the BT amount is located in D6,
- the VAT amount is located in D11,
which gives the following formula:
= D6 + D6*D11
- To calculate the TI amount for article 34FR453, select the cell corresponding to this calculation (E6) and then enter the formula (either by starting with the equal sign or by clicking the formula button).
The spreadsheet is, therefore in the formula entering mode, and:
- either enter the formula directly with the keyboard
- or build the formula by selecting the cells one at a time
In the second example, clicking on cell D6 will automatically add its name to the formula bar. Another click on this cell will display D6+D6 because addition is the default operation in the spreadsheet. Push the * button to choose multiplication, and then select cell D11.
To finish entering the formula, push the Enter button on the keyboard or use the Enter button on the formula bar. The spreadsheet will display the result of the calculation in cell E6:
How to copy formulas?
Imagine that we want to copy this formula and use it for the other articles.
- Select the formula in (cell E6), copy it, and select the block of cells from E7 to E9 (written E7:E9) and paste the previously copied formula. Here is the result:
- Note that the copied formula does not produce the correct amounts:
- In effect, when clicking on one of these cells, it can be noticed that the spreadsheet references the correct price before tax but that the reference to the cell with the VAT value is wrong (D13 instead of D11).
- This is because the previously entered references were relative references. The spreadsheet performs an automatic offset on cell references when copying the formula. Thus, to avoid the spreadsheet offsetting the cell with the VAT value, use an absolute reference ($D$11). Let's redo the formula:
- The spreadsheet now gives a coherent result:
How to use functions in formulas?
Functions may also be used in formulas, thus allowing advanced calculations. Most spreadsheets offer many integrated functions (Excel has more than 400).
Let's redo the above example. To automatically calculate the pre-tax total and the TI total of orders, use the Sum() function. To do this, select the cell to display the sum and enter the formula (starting with the = sign) using the Sum() function. There are many ways of entering the Sum() function:
- by manually entering it in the formula bar
- by using the function button that allows the function to be chosen from a list:
- by using the sum button directly
Whatever method is chosen, the two arguments of the Sum() function must be specified, which means specifying the cells for which the sum is to be calculated. There are two further possibilities for the user:
- enter the references of the selected cells on the keyboard
- select the cells or the blocks of cells to be used
Here is the result calculated by the spreadsheet:
- Spreadsheet formula
- Formula in spreadsheet
- Electronic spreadsheet formula
- Spreadsheet Functions
- Use of '@' in formulae [solved] > Forum - Excel
- Copy data from one excel sheet to another: automatically > Guide
- Update dates in Excel spreadsheet: each year, formula > Guide
- Spreadsheets viewing formulas [solved] > Forum - Excel
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- How to take screenshot in Excel: shortcut, sheet, cell
- Fix arrows keys in Excel: Scroll lock, alternative method
- How to apply a function to multiple sheets on Excel
- Convert numbers to words in Excel: without VBA, formula
- How to change author name in Excel: spreadsheet, VBA
- How to enable VBA in Excel: Mac, Office 365
- How many IF statements can you nest in Excel
- How to display multiple columns in a validation list
- How to use Excel color cell if formula
- Check if a value exists in an array VBA
- How to use an IF statement to add 1 to total
- How to perform a partial cell match in Excel?
- VBA select case like: operator, string, statement
- Search and find using VBA in Excel
- How to run macro when data entered in a cell
- What is the VBA code to select last sheet in workbook
- How to insert file path in excel: cell, sheet
- Open and convert an Excel file in Notepad
- How to disable auto recover in Excel
- How to insert GIF in Excel: 365, sheet, VBA
- VBA Excel color codes: index number, list
- How to count names in Excel: formula, using COUNTIF
- How to enter multiple lines in a single Excel cell
- How combine IF function, SEARCH and ISERROR in Excel
- Run macro on opening: worksheet, workbook
- Create new sheet based on cell value: Excel, macro
- Recalculate Excel workbook before saving
- How to change a column to numeric in Excel?
- Repeat rows in Excel: based on cell value, VBA
- Insert a hyperlink in Excel: with text, to another tab
- Insert picture in Excel: cell, shortcut, using formula
- How to manipulate data in Excel: VBA
- How to automatically transfer data between sheets in Excel
- Select the default number of processors in Excel
- How to create a cascading combo box: Excel, VBA
- Most useful Excel formulas: for data analysis
- How to transfer data from one Excel sheet to another?
- How to add a number of days to a date in Excel
- How to add sheet to workbook: VBA, Excel
- Recover Excel file: previous version
- Mark sheet grade formula in Excel: template
- How to calculate VAT in Excel: formula
- Excel functions in French
- How to copy a Macro into a blank cell
- How to generate email notifications for Excel updates
- How to fill multiple Excel sheets from master sheet
- How to apply if function in Excel: with dates, with text
- Excel send value to another cell
- How to create calculator in Excel VBA
- Split a workbook into individual files in Excel
- Unlock password protected Excel file: Macro
- Select empty cell in Excel: VBA
- Excel export data: from one sheet to another
- Using VBA to find last non empty row: in column, in table
- How to create UserForm: in Excel, VBA
- How to connect VB 6.0 with MS Access
- Copy data from one Excel workbook to another
- Excel VBA add command button programatically
- How to use Excel auto numbering formula
- How to remove leading apostrophe in Excel
- How to change enter key function in Excel
- Compare two Excel sheets: and combine data
- How to insert a transparent image into an Excel document
- How to count occurrences of characters and numbers in Excel
- How to change Excel date format
- Conditional formatting with dates: in Excel
- How to copy data to another workbook: using VBA
- Delete duplicates in Excel: column, formula
- How to show or hide formula bar in Excel: VBA, shortcut
- Transfer Excel data from one sheet to another: VBA
- Send email with attachment Excel: VBA, macros
- Create a new workbook and copy data in Excel via a Macro
- How to change row color in Excel VBA based on value
- Auto generate serial number in Excel: VBA, formula
- Transfer a worksheet to another Excel workbook: without VBA
- Insert an image into an Excel comment box
- Shortcuts to insert in Excel
- Run Macros in Excel: online, shortcuts, VBA
- How to clear formatting in Excel
- How to create a timer in Excel VBA
- Credit summation formula
- How to create a drop-down list in Excel
- How to use conditional formatting in Excel
- How to split text in columns in Excel
- How to copy data to multiple worksheets in Excel
- How to insert multiple rows in Excel with a macro?
- Excel IF, AND, OR, and NOT functions
- How to insert blank rows using macros in Excel