33 Must-know Tips and Formulas from ‘Excel 2016: Advanced Formulas and Functions’ [Part 1]

I recently completed this training on Lynda.com, and found the following tips and formulas to be useful. I have summed up ~4 hours of learning in these 33 bullets.

Formula and Function Tips and Shortcuts

1. Displaying and highlighting formulas
   1. Use ‘Ctrl+~’ to show all the formulas in the excel
   1. Another method is to use ‘Show Formula’ in the ribbon tab
2. Using Auditing tools
   1.  Track all the impacted cells in the Excel
      1. Use Trace Precedents and Track Dependents in the ‘Formulas’ tab
3. Using entire row and column references
   1. Use ‘Alt+Equal’ to select entries in the entire row/column for addition
   2. Use comma between Sum formula to include multiple rows/columns
4.  Copying column formulas instantly
   1. Drag by the end of the column to copy the formula to the entire row
   2. Press ‘Ctrl+.’ to go to the end of the column
5. Converting formulas to values with a drag
   1. Use ‘Flash Fil’ under ‘Data’ tab to fill the column with similar values
   2. Right click on the column and drag it to the cell
6. Updating values without formulas
   1. put the value in a cell
   2. copy the cell and choose paste special
   3. The value will get updated by the number in the cell
7. Debugging formulas with the F9 key
   1. select a portion of the long formula,
   2. press F9 and
   3. you can check the result right away (applicable for Binary formulas like AND OR as well)
8. Enhancing readability with range names
   1. Select all the cells to be included in the formula and give it a name in the top-left space
   2. Use that range and put it in the formula
9. Tabulating data from multiple sheets
   1. ‘Ctrl+Right click hold and drag will create the exact copy of the sheet
   2. Group sheets and autosum on the final sheet
   3. The totals will be added on all the sheets
      (Tip – for inserting same formula on multiple cells – select those cells, insert the formula on the first cell and press Ctrl+Enter)

Formula and Function Tool

1. Hierarchy of operations in formulas
   1. Parentheses – ()
   2. Power – ^
   3. Multiply/Divide – *,/
   4. Addition/Subtraction – +,-
2. Formulas tab for locating functions
   1. Click on the formulas tab
   2. Select the specific function you would like to use and click on ‘Tell more’ to know more about the function
3. Insert function for learning more about unfamiliar functions
   1. Click on insert function on the ‘Formula’ tab and write a small description of what you want to do
4. Extending the capabilities of Autosum
   1. Select cells and double-click the Autosum button to add the sum to the cell below the column cells and after the row cells
      1. If you highlight the cells, you will have to click only once
   2. Select the cells and you can use any capabilities included under the ‘Autosum’ icon and you can work across multiple rows/columns
   3. Use ‘Ctrl+1’ for the format cells pop-up
5. Absolute and relative references
   1. Press F4 to convert absolute reference to relative reference
6. Using mixed references in formulas
   1. Put $ in front of the cell or number as required and not both the numbers
7. Using auto-calculate in the status bar
   1. Customize the top column header and the auto-calculate bar will work on the custom formats like dates as well
   2. This can be enabled by right clicking the status bar and selecting the functionalities
8. Using IFS for multiple conditions
   1. Use IFS to remove the need for multiple nested If statements
9. FormulaText formula can be used to see the formula in a cell

Power Functions

1. Tabulating data using a single criterion
   1. COUNTIF – Count the number of entries in a data set
      1. Use wildcards (asterisks, question marks) to count on basis of a condition
   2. SUMIF – Total the number of entries in a data set as per the criteria
   3. AVERAGEIF – Average of the number of entries in a data set as per the criteria
   4. COUNTIFS – Count on the number of entries in a data set based on multiple conditions
      1. The limit to this formula is 127 conditions
      2. The conditions can be logical statements and need not have to be hardcoded
   5. SUMIFS – Total the number of entries in a data set as based on multiple conditions
   6. AVERAGEIFs – Average of the number of entries in a data set based on multiple conditions
   7. MAXIFS and MINIFS can also work on multiple conditions to find the
2. To avoid double-counting use SUBTOTAL function with the appropriate parameter (Tip – 1 for Average, 9 for Sum)
3. An alternate to MAX and MIN are LARGE and SMALL respectively
   1. Use LARGE and SMALL with the INDEX to find a particularly ranked number (e.g. 2nd highest, 3rd smallest)
4. Use COUNTBLANK to count the number of blanks in a data set
   1. Convert the data set in a table to make the counting dynamic
   2. If the result of a formula is blank, the COUNTBLANK function will calculate it as a blank
5. Variations of count:
   1. COUNT – Counts only the cells with the numbers
   2. COUNTA – Counts all the cells with the date

Math Functions

1. TRUNC – Always drops the decimal portion
2. INT – Always returns a lower value
3. ODD – Nearest odd number moving away from zero
4. EVEN – Nearest even number moving away from zero
5. MOD – Come up with remainder after division
   1. Can also be used with conditional formatting e.g. color every 4th row, highlight 4th column etc.
6. RAND – Generate random numbers
7. RANDBETWEEN – Generate random numbers within a range
8. CONVERT – This function can be used to convert between units, e.g. km to mi, C to F etc.
9. AGGREGATE – Use this function to bypass errors and hidden data, and still perform the mathematical operation
10. Use ROMAN and ARABIC functions to convert the numbers from Roman to Arabic or vice-versa
    1. The limit from Arabic to Roman conversion is 3999, but you can convert any length number from Roman to Arabic

Note – The second part of the post can be accessed by clicking here.