OverviewStep 1 creating a seriesStep 2 adjust row heightsStep 3 add column totalsStep 4 add a names columnStep 5 add column headersStep 6 add currency formattingStep 7 freeze the column headingsStep 8 add a Salary columnStep 9 add a conditional formulaStep 10 add a COUNTIF formulaStep 11 utilising binary cellsStep 12 applying Conditional FormattingStep 13 adding text cellsStep 14 adding an AVERAGEStep 15 adding calculations
Step 16 adding a FilterStep 17 add a Pie Chart graphicStep 18 using the IF and CONCAT functionsADDITIONAL Work with timesADDITIONAL PrintingAdvanced Topic VLOOKUPsAdvanced Topics Pivot Tables
Excel training
First written: Feb-2024
last changed Mar-2024

Using $ in cell formulas

Here we want to show the percentage of the average salary for each row.

  1. In a new column H2, add a formula dividing each of the values in D2 (salary) by the average found at the bottom of that column (D22) using the formula =D2/D22

  2. Drag the green border grabber down from D2 to D21.

  3. This will result in a value on D2 but a number of error #DIV/0! on the rest; if you look at D3 you will see the problem is that the formula is =D3/D23 ... and that D23 is not what we want.

  4. To fix this we should try again - on D2 type the formula =D2/D22; then on the formula bar at the top of the window click on the "D22" part of the text - then use the keyboard to click [F4] which will change "D22" to "$D$22" (repeated clicking on [F4] will switch it again to "D$22" to "$D22" to "D22" and then loop).

  5. Leaving the value as =D2/$D$22, drag the green border grabber down from D2 to D21

  6. Use the toolbar % icon to make the selected column values show as percentages - NOTE, this changes the values shown from a range 0 to 1 into equivalent percentages showing a range of 0 to 100 (i.e. 0.5 is shown 50%); also NOTE - the values behind these percentages are still 0-1 values.

  7. Use the toolbar Increase Decimal icon to make the new column show percentages to 2 decimal places

  8. Add a column heading of "% of Average"; make this heading cell wrap-text, and make the height of the row sufficient to fit the contents


Here we want to re-order the contents so that the list is in name order

  1. Click on B1

  2. Using the Editing tab from the window ribbon, select Sort & Filter and then Sort A to Z