Excel Tip of the Week Live - Your Questions Answered, Part 2

We recently ran our second free live Q&A webinar, with me answering your questions live.  The recording of this webinar is available here.  In this accompanying blog post, I will be giving written answers to all the questions we received, including those which we didn't have time to answer live.

How can you work with loan calculations in Excel?

There are a range of helpful functions for shortcutting standard loan repayment calculations in Excel - such as RATE, NPER, PV, FV, and PMT.  You can learn more about all of these in this Excel Tip of the Week post.

How can you do sensitivity analysis in Excel?

Briefly: Use a Data Table.  If you lay out the different trial inputs for up to two variables as row/column headings, and put the output function you want to monitor at the top-left, then Data > What-If Analysis => Data Table can get Excel to fill in the outputs for each scenario quickly and easily.  Read more in TOTW #218.

How do PivotTables work?

A Pivot is a crunched-down summary of a large table of data in Excel.  They let you easily summarise data without having to write formulas.  You can get started from Insert => PivotTable and experiment with dragging different fields to various locations and seeing how it works.  There's a tonne more going on here - see TOTW #190 for more or sign up for our upcoming webinar.

Do PivotTables come up automatically?

The option is available by default and a new one will automatically be inserted on a new sheet.  It's worth noting that, unlike formulas, Pivots don't update their values until a manual refresh is carried out.

What are the most useful keyboard shortcuts you use in Excel?

There are lots of good ones, but the ones that I probably use the most are Ctrl and arrow keys for quick movement, or the same while holding Shift for quick selection.

Do you have any sessions on advance modelling, in particular Monte Carlo analysis?

There's a full course on this available through ICAEW's training arm, the Academy of Professional Development.  However, one quick starting tip - if your model has some random elements in it, then a Data Table can generate a large number of randomised outputs easily. 

How do you automatically get a set number format when double clicking on pivot table?

The format used on one of these "snapshots" that you can make by double clicking on a figure in a Pivot is just the default format for new Excel sheets.  While it is possible to change that globally, it's more involved.

More generally, these snapshot reports aren't connected to either their source Pivot or to the original data, and so leaving them in a workbook can be a bit misleading as they aren't live data.

Can you show one more time what settings didn’t let addition of calculated fields?

The option I accidentally left clicked during the webinar is the bottom one, "Add this data to the Data Model".  This lets you do some neat things with connecting tables together and searching the fields and so forth, but it does mean you can't add calculated fields.

When you add a field into a pivot table - can you get it to automatically default to 'sum' rather than 'count'?

Excel will default to using Sum if the field is 100% numbers, and Count otherwise.  There's no way to change this.

Is there a simple way to input rules/macros which could hide worksheets with a use of an index page? i.e. you say which worksheets you need in the index page, and then it hides all the other ones.

There's certainly no simple way to do this, but there is a complicated way!  Create an Excel Table (with Ctrl T) in your index sheet with two columns like so:

Name this Table IndexTable, and then you can use this macro to hide/unhide each sheet according to the labelling in IndexTable, and then insert a button to run the macro:

Sub SheetHider()
Dim SheetName As String

For i = 2 To ActiveWorkbook.Worksheets.Count
    SheetName = ActiveWorkbook.Sheets(i).Name
    If WorksheetFunction.Index(Range("IndexTable[Visible]"), WorksheetFunction.Match(SheetName, Range("IndexTable[Sheet]"), 0)) = "y" Then
        Worksheets(SheetName).Visible = xlSheetVisible
        Worksheets(SheetName).Visible = xlSheetHidden
    End If
Next i

End Sub

Re pivot table -if you add rows to the initial data selection used in pivot tables, do you have to reselect the revised data length/ refresh or is there another way...

With ordinary data, yes you do have to go to PivotTable Tools and change the source to include the new rows.  However, if your data is in an Excel Table (Ctrl T), then new rows are automatically included.  See TOTW #163 for more on Tables.

Can you create a tab where all your favourite routines can be located?

When creating macros, whether via VBA or just having Excel record a series of steps you take, you have two options of where to store those macros:

  1. In the current file (if it is a .xlsm file); the macro is then available to anyone who uses that file; or
  2. In your Personal Macro Workbook, an invisible workbook that opens whenever you open Excel on a particular device and can be used to store macros that you want to run in multiple workbooks

Datedif function can calculate months or years. Is there a formula to calculate weeks?

The DATEDIF function is a legacy function from Lotus 1-2-3 and works like this:

=DATEDIF(start date, end date, "letter")

The letter can be d to calculate the number of days, m for months, or y for years; however DATEDIF has some known bugs.  However to determine the number of days between two dates in Excel, there's a simpler option:

=end date - start date

This works because Excel stores date values as serial numbers (counting up from 1 = 1/1/1900).  You can therefore calculate a number of weeks with just:

=(end date - start date)/7

How did you make the data set a table again?

Ctrl T, or Home => Format as Table.

I export TB's from QBO and copy the data into VT accounts. It works fine but is a pain when the N/L structure changes in QB (e.g. a new account is added or an account name changes. Then I have to be careful in VT to add new rows etc so that onward links work. Is there a better way to get data from QBO into VT?

It's hard to answer this question in detail without knowing the finer details of the programs in quetion, however Tables are a handy tool for making easily-extensible formulas that can handle changes, and the new dynamic arrays feature in Microsoft 365 is also pretty good at this.  If you post a more detailed question in the Discussions area or send to excel@icaew.com I can probably help.

Is there a way to copy a pivot table onto a new tab and then change the fields without changing the original pivot table?

If you just right click the tab and copy the sheet as a whole, the copy won't be tied to the original.

What does the error #VALUE mean?

Basically, this means that Excel has stumbled because something it was expecting to be a number wasn't one.  Note that Excel will sometimes automatically detect numbers formatted as text - for example =A1+B1 will add up those two cells even if one of them is formatted as text, because each is explicitly called in a mathematical operation.  But =SUM(A1:B1) only uses those cells' values implicitly, so it will only include the value of the cell which is not formatted as text.

Is there a way to look at active workbooks without having to close power query editor?

You can move the window a bit out of the way, but you can't properly interact with regular Excel until you're done and close the editor.

Can you say something about custom number formats?

These are written from the Format Cells menu => Custom.  There's a detailed language used to create custom formats, with lots of depth to it, but the basics are:

# for a digit that will be displayed if the number has a value in that location
0 for a digit that is always displayed
, and . are used literally
; separates the format to use for positive numbers and the one to use for negative numbers (and if used again, the format for zero values)
[colour name] can be used for a dozen or so common colour names

So for example, to use thousands separators, show two decimal places, and use red for negatives:


Plenty more in TOTW #332.

How can you find the cells that contain the links to workbooks which can not update and create an error when opening your file (probably trying to automatically update but the link is wrong)

You can search for them using Find on [ or .xls, but if that doesn't work (and the Data => Edit Links => Break Links tool isn't working either), then check for external links in named ranges, conditional formatting rules, and data validation rules.

How do I add back a removed column in power query?

You have to delete the step where the column was removed.

How would you create a macro (in personal macro book) so that it highlights all the data, and puts it into a custom number format (like the bracket number format)

Rather than making a macro for this, it's probably better to add your desired format as a custom Cell Style in your default Excel template, so that you can apply the Style in any new workbook you make.  There's a guide for doing this in TOTW #251 but essentially you need to make the changes in a blank file and save it as an Excel Template file in the Excel startup folder.

On Custom Number formats, can you save such a format to be your default on all spreadsheets please?

See above!

Can you add coloured cells ? (i.e. you can fillter coloured cells but can you have formulas for coloured cells?)

There's no inbuilt function for this, but I've written a custom function to do this that you could add in to your Personal Macro Workbook:

Public Function SUMIFCOLOUR(SampleCell As Range, AddRange As Range) As Double
Dim cell As Range

For Each cell In AddRange
    If cell.Interior.Color = SampleCell.Interior.Color Then
        If IsNumeric(cell.Value) Then
            SUMIFCOLOUR = SUMIFCOLOUR + cell.Value
        End If
    End If
Next cell

End Function

The formula syntax is:

=SUMIFCOLOUR(cell with the fill colour you want to sum, range you want to add)

And for good measure, here's one for counting the number of cells with a target fill colour:

Public Function COUNTIFCOLOUR(SampleCell As Range, CountRange As Range) As Long
Dim cell As Range

For Each cell In CountRange
    If cell.Interior.Color = SampleCell.Interior.Color Then
    End If
Next cell

End Function

Note that both of these functions will not work on cells coloured via conditional formatting; this is on a different layer and is invisible to all formulas and VBA.

If you have a cell that contains numbers and text, is there a simple way to break out the information into 2 separate cells, one containing numbers and the other containing the text?

If there's some kind of delimiter - a space or full stop or something - then Data => Text to Columns could do it easily.  If there's not, this would be quite tricky; at best a macro might be possible.

Can excel forms be used to collect accounting data (e.g. petty cash counts) and update a master spreadsheet?

Potentially - for example a simple table could be used to collect the data, and then a Power Query merge to join them together.  Depending on what you're doing, it might be easier to collect data using Google Forms or InfoPath or a similar tool instead.

When extract data for instance from NAV, if there are nil entries, there are in text form. Is there a method to convert the text to numbers so that formulas than then easily work with all the data in the columns?

A simple find & replace of nil => 0 could work.

Is there any way to download one page of resources...? There is no time to click on these all during the webinar

All the resources are available in the recording of the webinar, linked above.