Search Not Just Numbers

Loading...

Tuesday, 5 April 2016

ICAEW Spreadsheet Capability Framework - your opinions?

As you may be aware, I am a member of the Institute of Chartered Accountants in England and Wales (ICAEW) Excel Community Advisory Committee.

An important piece of work that the committee has been working on is what we are (currently) calling the Spreadsheet Capability Framework.

This document is intended to provide a reference point that spreadsheet users, employers and job applicants can use to discuss and assess competence in spreadsheet use - beyond glib phrases on a CV, such as "proficient with Excel".

Given the authorship of the framework, it is obviously primarily aimed at those in the finance field, so apologies to my non-accountant readers.

The framework has now reached the stage where we are actively seeking opinions outside the committee.

To this end my colleague, David Lyford-Smith has posted a copy of the document to the ICAEW's IT-Counts website, where your comments would be greatly appreciated.

If you would like to read the framework document and/or add your opinions to the debate, please use the following link:


http://www.ion.icaew.com/itcounts/post/Spreadsheet-Capability-Framework---exposure-draft-seeking-your-comments

Whereas comments on my blog are always welcome, and will be passed on, I would recommend commenting on the IT Counts post instead to contribute to the wider debate.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 8 March 2016

Excel Tip: Using Index to calculate a cumulative sum (e.g. a Year to Date total)

Let me start with an apology. My current workload has meant that my posts have been a lot less frequent in recent weeks. This may continue for a while, but I will still be aiming to post at least once a month during this busy spell.

In this post I want to show you how to do something I have shown before using OFFSET, but this time using the more efficient INDEX.

Let's look at the example of a 12 month budget spreadsheet with the monthly sales figures in cells B9 to M9 (month 1 in B9, Month 2 in C9, etc.). And let's say that the current month number is entered in cell B6.

Using OFFSET we could return the current month's sales using:

=OFFSET($B9,0,$B$6-1)

and the cumulative sales using:

=SUM(OFFSET($B9,0,0,1,$B$6))

See this earlier post, if you don't understand why.

The problem with OFFSET though, is that it is what is known as a "volatile" function. This means that it always has to be recalculated when any cell in the spreadsheet changes - as it does not specify a range that it is dependent on, therefore does not know whether a change might affect its result.

INDEX however looks at a defined range, making it significantly more efficient, and in its simplest form can be used to replace both of these examples of OFFSET.

For a range the width of a single cell (as in our example), INDEX only needs two arguments to return the current month's sales:

=INDEX($B9:$M9,$B$6)

This returns the value of the cell in position B6 in the range B9:M9.

If $B$6 is 3, this will return the value in cell D9, being the third cell in the range B9:M9.

To do the cumulative calculation, we can use the same INDEX function to return the end of  a SUM range, while fixing the start:

=SUM($B9:INDEX($B9:$M9,$B$6))

Again, if B6 contains 3, then this returns the sum of the range B9:D9.

In both cases, these will only recalculate if a cell in the range B9:M9 is edited. In a complex spreadsheet with many calculations, this can make a huge difference to calculation times.





If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 9 February 2016

Excel Tip: Controlling what is printed from a sheet

One of the challenges that many Excel users face, is ensuring that the printed version of a spreadsheet looks as good as it did on the screen.

There are a few simple things that you need to know to help to organise what actually gets printed and how it is spread across pages.

I will cover the first of these today, and revisit some of the others in future posts.

Today we will look at how to determine how much of the sheet is actually printed.

Left to its own devices, Excel will print all of the used area on the sheet, Specifically, it will print the whole area from cell A1 to the rightmost column and the last row that are not empty.

This may not be what is required, and it is easy to change. Simply highlight the area that you do want to print, then go to the Page Layout ribbon, click the Print Area icon, and select Set Print Area.

You can even select multiple ranges (holding down the Control key, while you select the subsequent ranges) which Excel will print on different pages.

For example, you may have a Profit & Loss and Balance Sheet alongside each other on one worksheet. Just highlight the area of the Profit and Loss (say A1:H100) and then hold down the Control key while selecting the Balance Sheet range (say J1:P80) and Set Print Area as described above.

Once a Print Area has been set in this way, you can see (and edit it) using the Name Manager on the Formulas ribbon. You will see all of the Print Areas in the workbook listed there and (scope to their particular worksheet). You can then edit these ranges like any other.

You can even make them a dynamic range using functions like OFFSET and INDEX, so that the area that gets printed is determined by values in the workbook.

A particularly useful technique is to combine this ability to set a dynamic Print Area with a formula to determine the last row of data. I explained a formula to do this in this earlier post.

Let's say we put this formula in a cell that we name LastRow.

If we want the print area to be columns A to J but the height of the print area to flex to the amount of rows occupied, we can enter the following as the Print Area (in the Names Manager):

=OFFSET($A$1,0,0,LastRow,10)

This will print a range starting at A1, with a height of LastRow, and a width of 10 (A to J is 10 columns).






Free Excel Dashboard Webinar
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 12 January 2016

Excel Tip: An easier way to change cell colours

Before I begin, I would like to wish a Happy New Year to all of my readers and subscribers - as this is my first post of 2016.

I am starting the year in humble mode, as I have had something very simple pointed out to me that I can't believe I had never spotted, despite it being right under my nose!

In my last post of 2015, I showed you some code to swap one cell colour for another throughout a worksheet. I then received a comment from a reader that I only know as Jim, pointing out that Excel already has a very simple way of doing this!

It can in fact be done using Find and Replace.

This amazed me because I use Find and Replace all of the time and didn't know you could do this!

It is simple as this:

  1. Highlight the range which you want to apply the changes to
  2. Click Find and Select (the binoculars icon) on the Home Ribbon and choose Replace from the dropdown menu
  3. Don't enter anything in the Find or Replace boxes, but click the Format button next to the Find box and on the Fill Tab, select the colour that you wish to change
  4. Click the Format box next to the Replace box and on the Fill Tab, select the new colour that you wish to swap for the colour you selected above
  5. Click Replace All
You can even start with the colour of an existing cell, using the dropdown on the format button, however be sure to remove any other formats other than Fill colour.

The thing that really shocked me is that these two big Format buttons have been right there in front of me every time that I've used Find and Replace since Excel 2003! And that's a lot of times!

In 2016, I am going to try to make sure that I constantly keep my eyes open for new ways to do things in Excel, instead of just relying on what I think I already know! And thanks again, Jim.





If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".