100 Excel functions - in order of how useful with details about how easy to master

A really useful checklist for topping up what you know.

Excel tables

Useful article about Excel tables - including their drawbacks

Enhanced aspects of Excel

Attended presentation by  Kate Watson for CIMA on enhanced aspects of Excel.

Good refresher on lookups, match. index, pivot tables with slicer etc.

Key points for me to follow up:

1. Using calculated field in pivot tables
2. Think more about using Indirect(cell name) and in that cell have a dynamic name that refers to a range of cells.  This could be really useful for data validation where second choice is dependent on first choice.
3. Power pivots (not covered but sounds like can be used to connect multiple data sources)

Kate said she would send out links to materials which I will link here when they arrive.

What's new in Excel 2016 for Windows - Excel

Lots of new toys here.  IFS looks especially useful.  What's new in Excel 2016 for Windows - Excel:



'via Blog this'

Useful article for brushing up on Excel sums and sumifs

The reminder about being able to use wildcards in sumifs became useful within a week of reading the article.  http://itsnotaboutthecell.com/2016/02/16/500-days-of-sumsumifs/

Using Excel to rank excluding duplicates

Purpose: when there is a field containing text values, with some blanks, rank the values but exclude duplicates and blanks.

Link to example spreadsheet

Excel chart tips: display hidden worksheet data and empty cells in a chart

The link below explains how to enable your chart to show blanks or zeros instead of missing data or continue lines from the previous point to the one after the missing point.

Display hidden worksheet data and empty cells in a chart:

Excel keyboard shortcut for strikethrough

Control + 5 is a quick shortcut for strikethrough.  It works both for whole cells and part cells.  Pressing the same again removes the strikethrough.

Excel data verification to ensure that the value in a cell starts with a C

Click this link to see instructions and screenshots.

Excel - avoiding cross casting errors (or SAP rejection due to decimals)

This is a good solution to those times when your data has say 3 decimal points but you are showing your numbers to 2 decimal points and so your totals do not seem to add up.  Excel will round the numbers to the decimal points you have shown.

Warning: remember to check that the total comes back to the number that you had in mind (if there is any cumulative rounding you may need to adjust one of your numbers somewhere along the way).

This is also really useful for SAP journal uploads where any numbers with too many decimals may be rejected.

Excel name box tips

Handy tips from the LinkedIn group 'Excel Gurus'.  Ever been at the bottom of a long spreadsheet, scrolled up to read the title but lost the column before you got to the top?  Typing c in the name box will select the whole column containing the active cell.

Excel subtotals and summaries

Some really useful ideas for Excel especially how to create manual subtotals which can be set up to exclude (or include) hidden numbers.

This could be especially useful where a single layout is used for presenting accounts each month and empty rows are hidden.  If the row is not empty the following month but is still hidden, a SUM formula would still add the number which could allow a balance sheet to balance at the bottom but the total to not equal the sum of the (shown) figures above it.  SUBTOTAL(109,range) gets round this problem or would allow a logical check to test this issue.

Excel tool to easily copy location of spreadsheets

If you use a network drive (or Evernote or Dropbox) adding the location tool to your tool bar will save you having to email spreadsheets.  Sending the link is much quicker and gives access to the latest version of a changing document.  The tool allows you to copy the location of the file with a right click and control C.

Adding the (file) location tool to Excel or Word

(Note that the destination of this link is in Evernote - if your employer blocks you from Evernote, you may have to view it at home but it is worth your time.)

Spreadsheet skills: reducing file size

A good explanation of how to deal with the issue when the bottom right of your spreadsheet is much further away than you expected (or it needs to be): Spreadsheet skills: reducing file size:

'via Blog this'

Excel Waterfall Chart - Tutorial and Template - Learn how to make waterfall charts using MS Excel | Chandoo.org - Learn Microsoft Excel Online

This is a good step by step guide to creating waterfall charts that look really smart.

Excel Waterfall Chart - Tutorial and Template - Learn how to make waterfall charts using MS Excel | Chandoo.org - Learn Microsoft Excel Online:

'via Blog this'

A handy tip that gets round mornings rounding down and afternoons rounding up

Excel Tip: Using Today() versus NOW() | AccountingWEB.com

Interesting article about Power Pivots - these are what will take over from Pivot Tables if and when we all upgrade to Excel 2010.   Excel extras: using PowerPivot

Excel Range Names

Everything you need to know about Excel range names | AccountingWEB.com