The problem with copied Excel charts

You have customised a chart, then copied both chart and related data to another area ready for new data. You then amend the copied data, e.g. to show sales of a different business and…. oh! There is a problem…

What you expect

The copied chart should be linked to the copied data – this is logical but not what actually happens.

What you get

The copied chart is linked to the original data so doesn’t change when you amend the copied data. The selected data for the copied chart must be manually edited. This is a pain.

And when you do that, you may well notice a second problem…

If you have edited the original chart e.g., changed line colours, added customised markers and data labels, these are mostly lost when you amend the data source in the copied chart. ☹

The workaround

You can copy the entire sheet, then cut and paste the data and graph on the copied sheet back to the original sheet, but this is inefficient. You may also have to edit the axis labels e.g., for years if these were linked to a row at the top of your (copied) worksheet.

The proper solution

Dear Excel development team at Microsoft… will you please fix this?

🗳 Your vote counts

Please like my related post on the official Excel community.

Excel calculation options and shortcuts

Calculation Options

If you have a large workbook with lots of lookup formulas or complex calculations the whole workbook can be slow to recalculate so you can turn on the manual calculation option.

There are two ways to do this:

  1. File, Options, Formulas


  2. Formulas ribbon, Calculation, Calculation Options (click the down arrow to open the dropdown)

In each case you will see three or four options:

  • Automatic (standard setting) - generally recommended so that you can see the effects of spreadsheet changes immediately.

  • Automatic except for data tables - recommended if you have data tables in your spreadsheet. Data tables are great for calculating the results of different scenarios to compare them but require multiple calculations of your model so this option is highly recommended in such cases to avoid slowing your model down unnecessarily.

  • Manual - recommended for large and slow workbooks - this makes it much easier to work with your file but you can't see the effects of changes immediately, hence you need to manually initiate a recalculation (see below).

  • Manual: Recalculate workbook before saving - this extra option is only available via File, Options. I recommend that you turn this off if you save your file often (e.g., so that in the case of a crash you have a recent copy of your file) but do not want Excel to recalculate your file each time as this can take a while with large, slow models.


Under File, Options you also get options for iterative calculations. This can be turned on if you have logical circular references on your model e.g., interest costs affect the closing bank balance which then affects the interest costs. The standard settings (100 iterations, maximum change 0.001) are generally good but, if necessary, you can increase the number of iterations and/or reduce the maximum change. Note that the accuracy here also appears to be used for Goal Seek calculations, so you can also make such changes to improve the accuracy of goal seek results.

 

Recalculation options and shortcuts

If you are using the manual calculation option, then you will have to manually start a recalculation at a suitable point in time (e.g. when you have completed a new model section). Here there are also a few options, some available with the ribbon/mouse, others with keyboard shortcuts as follows.

  • Calculate now (shortcut F9) - recalculates all open workbooks. Close unnecessary files to speed this up.

  • Calculate sheet (shortcut Shift F9) - recalculates only the currently active sheet. This is quicker than recalculating the whole workbook if you only need to update/check a single sheet.

  • Calculate a single cell (shortcut only: F2 followed by the enter key) - recalculates only the selected cell. This is the quickest option, but only a single cell, useful on large sheets where even a calculate sheet takes while. If you select a block of cells, you can press F2 and enter repeatedly to calculate each one in turn, Excel then jumps to the next cell.

  • Force full recalculation (shortcut only: Ctrl Shift Alt F9) – this can be useful on the rare occasions when model results do not appear to reflect recent changes you have made. To reduce calculation time Excel normally only recalculates dependents of cells which have changed (and their dependents etc.) plus all volatile functions such as TODAY, INDIRECT and OFFSET. Normally this works very well. Very occasionally however, it can be useful to force a full recalculation. This can take a while, so close unnecessary files before you start.

 

toolKCit add-in tools

For ease of use and additional calculation options and tools you can buy and install my Excel add-in toolKCit, available for a small fee at eloquens. Use the discount code HOW2EXCEL for 50% off.

  • Set manual calculation – sets the calculation to manual and no recalculate before saving. This is the same as going via File, Options but with a single mouse-click.

  • Calculate with timer – performs a normal recalculation (like F9) but tells you how long it took. Useful when you are trying to speed up your model and wish to compare alternative formulas for calculations to see which is quicker.

  • Force full recalculation – does the same as the shortcut mentioned above but with a mouse-click instead of the 4-key combination shortcut.

Gary’s Golden Ground Rules

😲 Nearly 90% of spreadsheets contain errors and many are also hard to use!
💰 These can be expensive in terms of money, time and reputation.

So how can you avoid these issues?

For my new book I went back to basics and completely overhauled my best practice advice. The result is my new, improved…

🥇 Gary’s Golden Ground Rules

These will help ensure that your spreadsheets are clear and easy to follow, which in turn reduces the risk of error during both development and usage.

1. Use a clear, logical workbook structure
Decide what worksheets you need, give them clear names and get them in order.

  • Spread content logically over worksheets
  • Organise worksheets into sections to reflect data flow from inputs to outputs
  • Include a cover sheet, ideally with hypertext links to facilitate navigation
  • Separate inputs, calculations and outputs

 

2. Keep your worksheets as clear and simple as possible
Ensure each worksheet is clearly laid out and easy to use.

  • Use a logical structure within each worksheet such as
  • Use consistent columns, row, formulas and whole sheets
  • Formulas
    • Ensure calculations flow from left to right and from top to bottom
    • Use the KISS principle: keep it short and simple
    • Follow formula priorities: (i) correct, (ii) understandable and (iii) short
    • Follow the COUNT principle: calculate once, use numerous times

 

3. Use a clear, clean, consistent design
Ensure the whole workbook has a clear, professional look and feel.

  • Use consistent (company-defined) fonts, colours, styles and data formats
  • Use the four key design principles: contrast, repetition, alignment and proximity
  • Clearly mark all inputs, for example as grey cells
  • Minimise non-data ink such as borders

 

4. Restrict access, inputs and changes
Ensure that only authorised users can access and change your spreadsheet and that inputs are valid.

  • Restrict access to the spreadsheet using folders with restricted access and passwords
  • Use data validation where relevant to help ensure inputs are valid
  • Consider protecting workbooks so that changes can only be made in input cells

 

5. Write instructions for users
Ensure that users know what to do and in what order e.g., how to update the model with new data.

  • Create a worksheet with an action list for users to follow
  • Add a status column to ensure progress is clear

 

Interested to find out more? My book contains lots more details and plenty of examples and screenshots. Check it out on Amazon.

Note: This link is to AmazonSmile – every purchase here generates a small charitable donation at no extra cost to you! 👍

Do you COUNT?

COUNT stands for ‘Calculate Once, Use Numerous Times’. That means if you need to use a value multiple times, you should calculate it just the once and then link back to that value in all subsequent calculations. This can make your calculations shorter, easier to understand and faster.

Here you can see the logic. Using the COUNT method is simply more efficient.

Here are two examples to show how this works...

Example 1: Actual vs plan sales, month to date

In this example I want to compare the actual month-to-date sales with the planned month-to-date sales for each brand. For the planned sales I only have figures for the whole month and need to multiply these with the percentage of sales days so far (the month-to-date %).

  • COUNT method: The month-to-date % is the same for all brands so I calculate the figure once and use the result for each brand.
  • Non-COUNT method: I calculate the month-to-date % multiple times, once for each brand.


The COUNT method is more efficient than calculating the percentage again and again for each and every brand and it speeds up calculations. This is particularly important for large or complex spreadsheets that can take a while to calculate.

Example 2: Sales by year for a selected region

This example uses INDEX and MATCH. Here we want to retrieve the annual sales values for a selected region.

  • COUNT method: The data for the selected region is always in the same row, so I only need to calculate this row number once (with MATCH) for use in all INDEX formulas.
  • Non-COUNT method: The row number is calculated again and again, in each INDEX formula.


Using the COUNT method, the formulas are much shorter than the non-COUNT method, and so are easier to understand and also faster.

This is just one of the many tips I explain in my book Avoid Excel Horror Stories. Read more here or buy it on Amazon.

Knott Consulting Gary Knott - Buch How to Excel

Complete Excel tasks in a second

This is the final blog in a series of three to help you save time when working in Excel:

  1. Save time with the best Excel shortcuts
  2. Save time with the Quick Access Toolbar
  3. Complete Excel tasks in a second (this blog)

You can complete Excel tasks in a second!

I was inspired by this  youtube video on how formula 1 pit stop times have been dramatically reduced and the accompanying LinkedIn post by Florian Palatini on the #SMED concept.

I have now adapted #SMED Single Minute Execution of Die for Excel… and speeded it up!

I call it #SSET = SINGLE SECOND EXECUTION OF TASK

🚀 Complete commonly performed Excel tasks in just a single second! 🚀

If you follow the advice in this blog, I estimate that you can easily save 10% of your time working in Excel. If you worked full-time in Excel, that would mean you would save four hours a week or 22 days a year!

Ready to learn?

On your marks, get SSET, go!

 

1) Preparation before the task is key

Keyboard shortcuts: Learn your shortcuts - see my blog or download a free list of the best Excel shortcuts to print out and refer to.

🖱 Quick Access Toolbar (QAT): For tasks where no shortcut is available, make sure you set up your QAT so that mouse-based tools and macros that you use regularly are lined up ready to use and always visible (no mouse clicks needed to show them!). In my blog I explain how.

Automation: For multi-step repetitive tasks, set-up Power Query, an office script or a macro to automate the actions. Store commonly-used macros in your personal workbook, so they are always available for use in any workbook and add those you often use to your QAT.

💥 Add-ins: Install one or more add-ins to add extra one-step buttons to further speed up your work. For a very modest fee, you can purchase my toolKCit add-in with 19 useful commands at eloquens. Use the discount code HOW2EXCEL for 50% off.

Or check out these other add-ins:
https://www.ablebits.com/
https://www.arixcel.com/
https://www.martforexcel.com/
https://macabacus.com/
https://www.numeritas.co.uk/download-nxt/ - workbook auditing
https://www.operisanalysiskit.com/ - OAK tool for workbook auditing

 

2) Use precise locations for everything

Ensure your keyboard is suitably placed in front of you so that you can easily use shortcuts. Similarly, ensure your mouse is suitably placed under your dominant hand to facilitate quick access to your Quick Access Toolbar (commonly used commands) and standard ribbon (infrequently used commands).

 

3) Follow a standardised procedure

For each task that you commonly perform, decide what your ONE method is: select this based upon speed and convenience

  • Keyboard shortcut (if available) or
  • Quick Access Toolbar or
  • Automated solution - Power Query, office script or macro
  • Add-in

In this way, you also save thinking time, which speeds up your work some more.

 

4) Practise, practise, practise

As the building site worker said to the old lady who asked, “How do I get to Carnegie Hall?”
“Lady, you gotta practise!”

  • Use your keyboard shortcuts and your Quick Access Toolbar as often as possible to make their usage second nature.
  • Practice common tasks such as navigating, selecting, formatting, inserting, copy and pasting cells, rows and columns.

 

5) Adapt the equipment

Keyboard shortcuts: Learn new ones, as necessary.

🖱 Quick Access Toolbar: Amend as necessary to speed up your work. For example, add any missing but commonly used commands, remove any unused commands (to reduce clutter and make space for others) or move commands in the list so that they are in an order that you find logical and easy to locate.

Automation: Amend your Power Query, office scripts or macros, as necessary.

💥 Add-ins: Research, try out and install new add-ins.

 

6) Never stop looking for waste

Strive to learn new functionality and tricks and tips to save you time. Review tasks that you commonly perform but which take you time and try to identify improvements e.g., reduce the number of actions needed, use a new shortcut or a new button on your Quick Access Toolbar.

 

So now you know the secrets of how to complete Excel tasks in a second!

Put them into practice quickly!