Advanced Excel – Advanced Formulas and Features
In Advanced Excel – Advanced Formulas and Features section, we will be discussing the advanced tools, features, and formulas that Excel has to offer after our explanation about excel formatting. Though some of the following features are not formulas or functions themselves, they are still useful, and can save time, when working with functions. We will first be exploring some techniques that allow you to easily work with formulas that were previously created, then we will see how range names help functions, and last, we will apply this knowledge in some advanced formulas that are contained in Excel.
When working with excel formulas , it’s useful to know when a cell is used in a formula and what formula it is being used in. There are some keyboard shortcuts that can help us do this. In addition, you can format formulas so that others looking at the sheet can tell which cells contain information from a formula. There is also a trick to quickly populate a formula down a column.
- Open the Simple_Budget.xslsx worksheet.
In this worksheet, there are some calculations that have not been labeled. Every cell in this sheet is either an output from a function or used in a function.
- Click into B2 and press ctrl +] on the keyboard.
This will show us what cells contain functions that reference this cell. In this case, there are only two which are both in the screen, but if not, we can use enter to navigate to the other functions.
- Press enter to move to the second formula that uses this cell.
- Click into cell C13 and press ctrl + [on the keyboard.
The active cell is now a cell that is used in the function which is located in C13. Pressing enter will also move between cells here similar to step 4.
- Find what the data in B11:B15 and C12:C15 is referencing and label them using
the closest empty cell.
Cell B11 contains the total rent, so you could put “Total Rent” in cell A11. When labeling, notice that the all of the data in C12:C15 are averages.
- Navigate to sheet 2.
In this sheet there are a list of tenants and some data about their rent. In D2, there is a formula for that tenants total rent, but we want this formula to be in the whole table. We could drag the formula through the table, but excel has a quicker method.
- Select D2 and double click the small box in the bottom right hand corner of the cell.
Notice the formula is now in column D for each row.
We know want to add the entire column into G1. We could navigate to the bottom of the column and enter the range that has data, but if any tenants are adding to this list, their rent would not be included.
- Enter “=Sum(D:D)” into G1.
This formula reads “sum the entire D column”. Using this notation, if any addition tenants are added to the list, they will be included in the formula.
- Type”=average(“ into G2, select column B and press enter.
Selecting the column is another way to specify that the values in the entire column will be used.
Now that we have some formulas in this sheet, we will look at a few easy ways to display those formulas. Your sheet should look like the following picture:
- On the keyboard press ctrl + `.
This shows all of the calculated formulas as functions.
- Press ctrl + ` again to return the values.
- In the editing group on the Home tab choose Find and Select.
- Choose formulas from the menu.
For easier viewing, one could format the formulas with shading. All of the cells containing formulas are now selected.
- Shade all of the formulas with a color.
If you wanted to add to cells, but did not want to use a formula, special pasting can help. We want to add 6 months to every cell in column C.
- Type 6 in cell E1 and copy the cell.
- Select column C and press ctrl + alt + v on the keyboard.
This opens the special pasting dialog box. Some options are available on a right click, but we need more specific options.
- Choose Add and skip blanks, then click OK.
This told Excel to add the copied value to all of the cells in this column. Notice the text data does not change.
Suppose you wanted the data in column D to be displayed, but you did not want the formula contained in the sheet. Using special pasting, we can remove all of the formulas from column D.
- Select and copy column D.
- Right click on column D.
- Choose the special pasting menu.
This menu contains many different pasting options . If more options are needed, choosing Paste Special from here would bring up the special pasting dialog box which we saw earlier.
- Click on the values option.
This option, , tells excel to only paste the values from the copied selected cells.
cells into the now selected cell.
- Click a few of the cells in column D to observe that the data value is now simply a value.
The downside to doing this is that if you edited the Number of months data, column D would not be updated.
We have just explored some time saving techniques to use with functions and formulas in Excel.
The next subsection talks about how naming ranges can save time with referencing in functions.