Easy-to-enter sheet and file name formulas

Here’s a great trick using autocorrect to easily enter a formula to show the name of the sheet or file. Set up once, use as often as you want with a simple two-letter entry.

I use these formulas a lot e.g., to create a flexible template sheet for my Excel models. When I copy the template and rename it, the sheet name (say in cell A1) automatically updates… fantastic!

How to set up
Here’s how to set it up for sheet name (a one-off exercise)...

  1. Copy this formula if you have commas as separators
    =MID(@CELL("filename",A1),FIND("]",@CELL("filename",A1))+1,256)
    or this one if you have semi-colons as separators
    =MID(@CELL("filename";A1);FIND("]";@CELL("filename";A1))+1;256)

  2. Go to File, Options, Proofing, AutoCorrect Options…


  3. Under “Replace:” enter SN (for sheet name) and under “With:” paste the formula you copied in step 1, then “Add” and “OK” twice.

To set up the filename formula follow the same steps but replace FN (for file name) with this formula if you have comma separators
=MID(@CELL("filename",A1),FIND("[",@CELL("filename",A1))+1,FIND(".xls",@CELL("filename",A1))-(FIND("[",@CELL("filename",A1))+1))

or this one if you have semi-colon separators
=MID(@CELL("filename";A1);FIND("[";@CELL("filename";A1))+1;FIND(".xls";@CELL("filename";A1))-(FIND("[";@CELL("filename";A1))+1))

How to use

Simply go to a blank cell, type SN (space) and enter and you have your formula for sheet name!

Or type FN (space) and enter and you have your formula for file name!

(Technical tip: these formulas use the CELL function which only works if you have saved your file, because only then does it have a full path name).

Have fun with them!

Bar chart with values above/below minimum

You have, say, plan bank balances for different scenarios and also a minimum or target value.

Wouldn’t it be great, if you could show these clearly in a chart and the bar colours would automatically change, depending upon whether the scenario value was above or below the minimum?

Well, you can! I will now show you how, step-by-step.

1. Set up your data with two extra rows

  • Have scenario values in one row and the minimum values in another

  • Two extra rows should show values (i) equal to or above the minimum and (ii) below the minimum. We will show these as two separate series in the chart, so that we can give them different colours


    2. Create your chart
  • Insert a stacked bar chart (not a clustered bar chart)


  • Right-click in the chart and choose “Select Data…”

  • For the Legend Entries (Series) select or add the three series: Above minimum, Below minimum and Minimum


  • For the horizontal-axis values add e.g. the scenario names

    3. Format your chart bars and line
  • Right-click on a bar for the “Minimum” series and choose “Change series chart type…”

  • For the series Minimum select “Line”


  • Right-click on a bar for the series “Above minimum” and select “Format data series…” or left click and use the shortcut Ctrl 1

  • Change the colour e.g. to light green

  • Do the same for the series “Below minimum” and change the colour e.g. to pale red

  • Do the same for the line series “Minimum” and change the line colour e.g. to dark red and maybe make it a dotted line


4. Add and format data labels

  • Right-click on each of the three series in turn and select “Add data labels”

  • To avoid zeros appearing for “Above minimum” when the value is below minimum (and vice versa) format the data label numbers so that zeros do not appear: right-click on a data label and select “Format data labels…” or left click and use the shortcut Ctrl 1. Enter the custom format #,##0_(;(#,##0); if your thousand separators are commas or #.##0_(;(#.##0); if they are dots and click “Add”. This format has three components: positive numbers; negative numbers; zero (blank)

  • Finally you can select individual data labels for the series “Minimum” and delete all but the last one, which you can format to show to the right



5. Add chart title and tidy up the legend

  • Add a clear chart title include units (e.g. EURk)

  • Select and delete the legends for “Above minimum” and “Below minimum” as the chart title is clear enough

 

Et voilá… your great chart is complete!

If you want, you can save your chart as a template and use it again.

 

For general tips on the best chart type to use, see my best chart blog.

All models are wrong, but some are useful

This quote is from a paper by British statistician George E. P. Box published in 1976 called “Science and Statistics”. Although primarily made in reference to the fields of statistics and analytical models, it can also be applied to financial and other models created in spreadsheets.

A model is a simplified representation of reality. The act of simplification means that all models are wrong because they do not reflect all elements of reality. To have complete accuracy would require e.g., a map on the scale of 1:1. This may well be accurate, but it would not be useful as you could not do any sensible route planning with it – it would be simply far too big and unwieldy.

On the other hand, if we accept a loss of some accuracy, we can have a model which is “wrong” but useful. In physics, for example, the Bohr model of the atom is “wrong” but can nevertheless be very useful in predicting and explaining events in the real world.

The same principle can be applied to spreadsheet models. The key is finding the “sweet spot of usefulness” between accuracy and complexity on the one hand and simplicity and ease of use on the other.

This is not always easy but, as with most skills, you can get better with experience.

Here is my advice to help you:

1.

Think about the level of detail you need in your model before you start work on it.

If appropriate, discuss with colleagues, model sponsors or users before starting.

Always bear in mind:

(i) the model purpose – what content and functionality is relevant and useful?

(ii) the users – what functionality will they understand and be able to use reliably and efficiently (possibly after training)?

2.

Focus on the essentials and use the 80:20 rule.

Often, 80% of the results can be explained by the 20% most significant factors, so focus on these. Minor costs (for example) should not be planned individually but in aggregate as part of an “Other costs” position.

If appropriate, focus on the top 10% of factors first to get a working model up and running which can be used for early decision-making and expanded later.

3.

Before adding more complexity, think about how significant the effects could be on results or usability.

If the increase in complexity would bring only a minimal increase in accuracy or functionality, then maybe you should not make the change. Or perhaps a simplified version would suffice.

For example, planning sales at individual product level may be too detailed. Planning at brand or business area level may be more suitable.

Once you have made your decisions, be ready and able to explain them.

 

I will conclude with another quote from George Box which summarises the situation very well.

“All models are approximations. Assumptions, whether implied or clearly stated, are never exactly true. … So, the question you need to ask is not "Is the model true?" (it never is) but "Is the model good enough for this particular application?"

Calculate sums and subtotals

Use both SUMs and SUBTOTALs to help understand your data!

  • Place SUMs for key data columns such as sales values above your data where you can easily see them and not below your data, which forces you to scroll down – that is inefficient, especially if you have long lists.

  • Also calculate SUBTOTALs. SUBTOTAL is a very useful and flexible function which shows you the sum of your filtered data, very handy when checking or analysing your data.

The SUBTOTAL function can give various results depending upon the number you enter in brackets before the data range. Most commonly you want the sum, which requires the number 9. Other possibilities are average, count, maximum and minimum. As soon as you type SUBTOTAL( you get a drop-down list of all the options.

I recommend that you calculate both SUM and SUBTOTALS for key data columns in two separate rows, as shown in the screenshot. The SUM result showing the total of all amounts in the column is then always visible and can be linked onwards in the workbook (if necessary) and the SUBTOTAL can be used for filtering and data analysis. If no filters are set, the two formulas should give the same result.

Note: In the example in the screenshot I have used a table but both SUM and SUBTOTAL functions also work with data which is not in a table. In this case, simply enter the cell references as your data range.

Excel space tip

Did you know, you can add spaces to formulas to make them easier to read?

Well, you can, and you should! 😊

Inmedievaldaysbookswerewrittenbymonkswithoutspacesbetweenwords.

Did you find that easy to read? Of course, not – it has no spaces. But this was standard practice in medieval books. Over time, however, spaces were added between words to make texts easier to read.

When writing spreadsheet formulas, however, we are still mostly following the medieval monks. Surprise, surprise, this means that formulas – especially those which are longer or more complex – are harder to read. And harder to write, check, amend and understand.

Therefore, I recommend you add spaces to your formulas e.g., after every argument separator (a comma or semi-colon, depending on your regional settings) or before every plus-sign. This makes them easier to read etc. and has no impact on results as Excel ignores them.

If you want to “retrofit” spaces into your formulas, make a back-up of your file then simply search and replace all argument separators (a comma or semi-colon) with a separator followed by a space.