Create dynamic cards and chart titles

Here are two great ideas to create dynamic outputs e.g. for your dashboards: KPI cards and dynamic chart titles that automatically update when your data changes!

 

KPI CARD

A KPI card reports a single key value such as sales to date or a KPI - simple but great! They are common in Power BI, but you can also create them in plain old Excel. Here’s how.

  • Insert a text box (Insert, Text box) or shape (Insert, Shape) - I like rounded rectangles – and format it as you wish.
  • Select the shape and in the formula bar enter =(cell reference).
  • If this is a number, the format in the text box or shape is the same as the source cell. 😊
  • Extra tip: You can then add text around the number by double-clicking in the shape and typing!

 

CHART TITLE

Add a dynamic title to a chart e.g. with a date. Instead of accepting the default chart title or manually typing in your own title, you can link a chart title to a cell.

  • Simply select the chart title box and in the formula bar enter =(cell reference).
  • You cannot create a formula or use any functions here. But you can do that in the source cell…

  • In the example shown, I want to include the latest date in the chart title. You can get this automatically using the function MAX on the date column in your source data or (as shown here) use an input cell (cell B25).
  • I have created a dynamic title (in cell C25) using the date (in cell B25), which then appears in the chart title. Fantastic!
    • You can build this text in parts and join them using &
    • If you refer to a number or date, you must format this using the TEXT function.
    • To get the correct format, first select a cell with the format you want, go into format cell (shortcut Ctrl 1); on the “Number” tab, select “Custom”, then copy the ‘format code’ from the Type box.
    • Paste this into your TEXT formula inside quote marks.
    • In the example shown, the final formula is
      ="Sales month to date (EURk), as at " & TEXT(B25;"DD.MM.YYYY")

  • Extra tip: You can hide your dynamic title cell behind the chart if you want to.

 

THE FINAL RESULT

Define and use range names

Create range names

Select a cell or range of cells, then type in a short but descriptive range name in the Name Box (above cell A1, next to the Formula Bar) e.g., Start_date or Tax_rate. The name may not contain spaces (use an underscore if necessary) and certain names are not allowed e.g. J5 as this looks like a cell reference.

 

Use your range names

Here are three practical uses.

  1. Simplify formulas

Simply start typing your range name in your formula and it will appear in the list along with function names (see screenshot above). Alternatively, press F3 to get a list to select from. This can make your formulas easier to read. In the example shown, a payment date cannot be in the past i.e. before the start date of the tool.

Karen Roem also likes the fact that the name does not change when you copy the formula, in contrast to a 'normal' link to a cell which you have to fix with dollar signs.

 

  1. Easily select a range to copy

Here I have defined a range called AR which covers all the entries in the AR report. I can select this named range using the Name Box (above cell A1, next to the Formula Bar). I can then copy this (manually or using a macro) e.g. to another range AR_old, to allow comparisons to be made.

 

  1. Aid navigation

(i) Use the Name Box

Simply use the Name Box dropdown (above cell A1, next to the Formula Bar)  and select a range name to jump to it.

(ii) Create a hyperlink

Alternatively, create a hyperlink using Insert, Link, Insert Link… (at the end of the list), Place in This Document, select a range name (under “defined names”) --> a clickable hyperlink is created in the cell. Simply click on it to jump to the range!

Bonus tip: You can copy this hyperlink to other places in your workbook if desired.

 

Edit and manage your range names

If you need to review or amend your ranges, simply use the Name Manager on the Formulas ribbon.

Protect your spreadsheets!

To reduce the risk of user error, you can protect your worksheets so that users can only change input cells and perform certain other restricted actions such as select cells and use filters. To enable this is a two-step process, as follows.

1. Ensure non-input cells are locked and input cells are unlocked

🔒 Non-input cells such as formulas and labels
By default, all cells have the status “locked” (protected). If you want to check, select all relevant cells, select Format Cells (shortcut Ctrl 1), select the “Protections” tab and check there is a tick next to the “Locked” property.

Hidden: The protection tab offers an additional option, ‘hidden’. This allows you to hide the cell formula in the formula bar when the cell is selected. In the interests of transparency and understanding a spreadsheet, I do not recommend this option.

🔓 Input cells
You must unlock the input cells (including those with data validation).

First, select the input cells. If you are not sure which cells are inputs, use GoTo (shortcut F5). Select “special” and “constants”, “numbers”, then ok. All input cells with content are now selected!

Then select Format Cells (shortcut Ctrl 1), select the “Protections” tab and remove the tick next to the “Locked” property.

Bonus tip #1: Use cell styles to easily remove protection for input cells

The easiest approach to unlock input cells is to use cell styles. Format an input cell with "Locked" turn off. Then go to the Home ribbon and click on the down arrow at the end of the the Styles section.

Select "New Cell Style..." and save the format as e.g. "01 Inputs" (numbering ensures your cell styles appear in your desired order).

Now when you want to format an input or drop-down cell in your model, simply select the cell(s) and click on the relevant style name in the styles box on the home ribbon. The cells are then given the appropriate colour and are automatically unlocked… simple and effective!

I typically use one cell style for inputs and another for drop-downs with a different colour, both of which are unlocked, i.e., the ‘locked’ option box is not ticked.

See Microsoft support for more details on cell styles.

Bonus tip #2: Use background error checking to identify missing protection

You can turn on background error checking for ‘Unlocked cells containing formulas’ under ‘File’, ‘Options’, ‘Formulas’, ‘Error checking rules’. Excel then automatically marks any unprotected cells with a green corner, and you can then correct the cell formatting, as explained above.

2. Turn protection on for each worksheet

This step is hinted at by Excel in the note underneath the protection settings. ‘Locking cells … has no effect until you protect the worksheet.’ This is simple enough to do.

🔐 Select the relevant worksheet, navigate to the Review ribbon, click ‘Protect Sheet’.

Then select what you want to allow. ‘Select locked cells’ and ‘select unlocked cells’ are allowed by default – generally sensible – but can be deselected. All other options are deselected by default but you can tick an option box in each case. One I generally allow is ‘Use AutoFilter’, especially for data analysis files or in models with large input sheets.

The final step is to input a password (if desired) and you are done. Obviously, you must remember the password, which can become a problem if you have lots of protected spreadsheets in use. You can therefore consider protecting the worksheet without a password to avoid problems.

Repeat for all further sheets in the model that you wish to protect. If you use a password, I strongly recommend using the same password for all protected worksheets in a single workbook otherwise the pain of password management or the risk of forgetting a password becomes too great.

🔓🔐 If you remove worksheet protection to make a change, you must re-protect it afterwards!

I am afraid there is no option to turn the protection on (or off) for all worksheets at once, so this task quickly becomes time-consuming, unless you use a macro…

Macro to the rescue!

I use a macro to protect all worksheets in a workbook and another to unprotect them all. To get this, please download my free bonus material for my book "Avoid Excel Horror Stories". The VBA macro code is in the main file ‘how2excel – example calculations and macros’.

The ‘protect sheets’ macro also solves another problem with protected sheets: by default, you cannot open and close row and column groupings on protected sheets (an Excel bug), which can be frustrating. As well as protecting sheets, the macro provided specifically enables the opening and closing of row and column groupings.

Bonus tip: Add password protection to the macros to prevent unauthorized users finding the password in the macro code! In Visual Basic editor, right-click macros, select VBAProject Properties, Protection tab, Lock project for viewing, enter password twice.

Extra protection: workbook structure

On the Review tab next to ‘Protect Sheet’ there is an icon for ‘Protect Workbook’. This enables you to prevent users changing the structure of a spreadsheet (e.g., moving, renaming, deleting or adding worksheets), which may be worth considering.

RESULT

In the suitably protected worksheet shown above, users can only enter and change values in the grey input cells. If they try to change any other cells, they get an error message like this.

Don’t confuse these calculations!

Profit percent, percentage changes and average prices – all three are very useful for business but for each one there are two calculation methods so there is a risk of confusion and error.

In this blog I go through all three and in each case I explain the two calculation methods and recommend which one to use.

Let’s go…

 

1. Gross margin % or mark-up % ?

Both of these figures are related to the gross profit, but they serve different purposes and are calculated differently.

Gross margin %

  • Margin tells you what proportion of your sales is left as gross profit.
  • It is useful for comparisons e.g., of two different products, two business units, two businesses, two years, or plan and actual figures. It can also tell you how much you can reduce selling prices by and still make a gross profit.
  • The calculation is gross profit / sales.
  • In the example shown above this is 400 / 1000 = 40%.
    That means that for every 100 Euros of sales, you are left with 40 Euros of profit (i.e. 40%).


Mark-up %

  • Mark-up on the other hand tells you how much you add to your cost of goods sold (COGS) – i.e. what it costs you to buy or produce the products or services you are selling – to get your selling price.
  • It is useful for calculating or assessing selling prices and is typically applied to individual products or groups of products.
  • The calculation is gross profit / COGS; if your COGS figure is shown as a negative number, you can add a minus sign to your calculation so that the result is a positive number.
  • In the example shown above this is 400 / 600 = 67%.
    That means that for every 100 Euros of costs, you add 67 Euros on top to get your sales price. For 600 Euros of costs you would add 600 * 67% = 400 Euros, which represents your gross profit.


Recommendation

In general, I recommend the margin %. Sales prices are often set in comparison to competitors (market-based pricing) or what you believe the value to be for your customers (value-based pricing). The concept of mark-up is then not relevant, but of course, you should still consider your costs.

The margin % is also consistent with a “single size profit and loss account (P&L)” analysis where all P&L items are calculated as a percentage of sales. This extends the approach for comparing e.g. two years or two businesses from comparing just the gross profit to comparing all P&L lines.

 

2. Change in percentage points or percent?

A change in percent is often used to plan or assess e.g. costs. If you are analysing monetary values, there is usually no confusion. If costs have risen form 1,000 Euro to 1,200 Euros then we have a 20% increase = 200 Euros rise / 1,000 Euros.

Things can get confusing however when you compare the change in a percentage figure, such as (i) cost of goods sold (COGS) as a percentage of sales or (ii) interest rates, as shown in the example below. Here the interest rate has risen from 10% p.a. to 12% p.a. (unrealistic but simple figures for the purpose of illustration).

Note: I took this example from the website https://www.mathsisfun.com, so thanks to them!

Here we can calculate and talk about the change either in percentage points or in percent and we must be clear which one we are using.

Change in percentage points

  • The interest rate has risen from 10% to 12% so the change is simply 12% - 10% = 2% i.e. 2 percentage points and not 2 percent.


Change in percent

  • The change in percent however is 20%, because the rise of 2% points is on top of the old rate of 10% = 2% / 10% = 20%.
  • If we examine the interest costs, these have risen from 1,000 Euros p.a. to 1,200 Euros so we also have a 20% increase = 200 Euros rise / 1,000 Euros.


Recommendation

Both figures are correct, but you must be clear about which one you are using. In general, we are interested in the impact in monetary terms, which the change in percent figure tells us.

As the mathisfun website says, “When in doubt, use both. For example, say 'Interest rates increased by 2 percentage points today, meaning a 20% increase in interest payments.'"

 

3. Simple or weighted average?

In this final example we are selling two products: a small widget and a large widget. The small version sells for 10 Euros each and the large version for 20 Euros each. What is the average selling price?

Simple average

  • If you have no information on numbers of units sold (or you choose to ignore them), then you can calculate the simple average = (10 + 20) / 2 = 15 Euros
  • This is really only true if you sell the same number of units of each product, which is probably not the case.
  • In most cases, the simple average gives an incorrect understanding of the average price as we have not considered the numbers of units of each product sold. When we take these into account, this can have a dramatic impact on the average, as we will now see.

Weighted average

  • The correct way to calculate the average selling price is to take the total sales divided by the total number of units sold.
  • In this case we have 1,000 Euros total sales divided by 95 total units sold which gives a weighted average selling price of 10.53 Euros.
  • This figure is much lower than the simple average of 15 Euros because it has been weighted by the number of units sold: we have sold many more units of small widgets than large widgets, so the weighted average price gets weighted towards the small widget price of 10 Euros.

Recommendation

Use the weighted average. To calculate this, you need to use total figures, as shown in the example above.

If you don’t have enough data to calculate the weighted average, state that the figure you show is a simple average. And don’t use it for calculations.

Custom lists help you enter and sort data

Standard Excel

In Excel you can easily create a list of months by typing in the first month and then dragging the cell down (or to the right) using the small square (handle) in the bottom right of the cell. This also works for abbreviated month names (first three letters off the name) as well as days of the week. This is possible because Excel has suitable lists available for such purposes. You can also sort data using columns containing days or months.

Customised Excel

Wouldn't it be great if you could do the same with a customised list of e.g., brands, regions or priorities (high, medium, and low) in use in your company?

Well, you can!

Set-up

First type in your customised list in Excel, then go to File, Options, Advanced, General section, Edit Custom Lists…

You can then import your custom list. Alternatively, you can type in your list directly in the “List entries” field.

Use

Back in Excel you can type in one item in your custom list and then drag the handle to complete the list.

You can also sort using your custom list.

1. Select the column in your data containing the items in your custom list.

2. Next, click on the main sort icon

3. Under “Order” choose “Custom list…”
Finally select the relevant list and click ok.

Your data is now sorted according to your custom list.

Fantastic!