Objectives

Students will be able to:

  • use absolute references in formulae
  • double check ranges
  • possibly create a clustered chart.

Ab$olute reference$

Find cell D18. How do you think that number was calculated? Click to find out.

But... what will happen if we copy down this formula to complete the summary data? Click to find out.

So, in certain situations we need to lock some cell references in a formula before we copy down. As the designer, you need to figure out which references need to be locked. These are called absolute references.

To fix a reference, simply attach a dollar sign infront of the cell's letter and number cell reference. What do you think we need to change the formula in cell D18 above before we copy down? Click for the answer.

   Task

If you are using Google Sheets, use this sheet.

If you require a csv file to get started, download this one.

Follow these instructions to develop the spreadsheet model:

In cell H3, create a formula to calculate the total bonus. This is the Number of Sales multiplied by the Bonus Per Sale. Copy down the formula for every other row in the data set.
In cell M7, create a formula to calculate the Total number of sales for Barry Lin. Use absolute references where required. Copy down the formula for the other two employees. Double check your work. Press CRTL~ on your keyboard to enter formula view.
In cell N6, create a new heading called Total Bonus Per Staff and calculate the total bonus for each staff member in that column. Make sure you consider which cells require an absolute reference. Copy down and double check your result.
In cell N4, write the word Tax and in cell O4 type 0.05. Format it as a percentage.
In cell O6, write the heading Tax amount. This is the Total Bonus multiplied by cell O4, the tax rate. Think about when to use absolute references in your formula and then copy down. Double check your result.
In cell P6, write the heading Net Bonus. This is the Total Bonus minus Tax amount. Think about whether or not to use absolute references and copy down. Double check your result.
Format the summary data section so that borders are visible on all of the summary data and title cells.

Merge the 4 cells of the title of the summary data section. Center-align the title and make it bold with a red background.

Make sure all data in your spreadsheet model is visible.

Reproduce this combo chart (sometimes called a clustered chart). Note, 2 data sets are represented and there is a secondary axis.

Submit your final spreadsheet file as instructed. BEWARE of saving your work in a CSV file - all of your formatting, functions and chart will be lost!!

Food For Thought

Copy down is a convenient feature of spreadsheet software. However, it cannot predict which cell references need to be fixed/locked/absolute. You, the designer, should have experience to make that decision. Hopefully this course is giving you that experience!

Homework

Tags

spreadsheet data csv model merge insert row format currency column range decimal places cell reference

SUBSCRIBE

Join my mailing list to receive updates on the latest blog posts and other things.