Search Not Just Numbers

Tuesday 28 May 2013

Excel Tip: A really quick way to copy down a formula

Just a quick tip this week, as it was a Bank Holiday yesterday in the UK, and I now have four days to fit a week's work in!

This is a really simple one, but I've lost count of the amount of times I've used this in front of an experienced Excel user who has stopped me and said "What did you just do there?".

Like all simple things, it's only simple if you know it!

This is a really easy tip in Excel to copy a formula down to the bottom of your data - without scrolling.

There are many times - particularly if you lay out your data as per my advice in earlier posts - that you wish to copy a formula down to the bottom of your data. If you have thousands of rows of data this can be a tedious and time consuming task, as you scroll down page after page.

But it doesn't have to be like that. Simply select the cell that you want to copy down and double-click on the black square at the bottom right of your selected cell (as highlighted in the image above).

If the rest of this column is empty, it will copy the contents down to the bottom of the data in the column adjacent to it.

If you already have a formula copied all of the way down, and you edit the top one, this will copy it to the bottom of this column (overwriting the older version of the formula).

That's it for this week. Simple, but really handy if you didn't already know about it.

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Tuesday 21 May 2013

Excel Tip: Managing Deadlines in Excel

Today's post was prompted by a discussion on the accountants website, AccountingWeb.

One accountant was looking for a simple way to manage client deadlines in Excel, for Corporation Tax returns, VAT Returns, Companies House Annual Returns, etc. Although this example is for accountants, it can be applied to managing any sets of multiple deadlines.

Although there are all sorts of packages out there for doing this kind of thing - and I would suggest using those if your needs are much more complicated than this example - if kept simple, Excel might be all you need.

I have provided a finished (simple) example but will talk you through how to build it.

You can download the finished example, here:

DeadlineManagerExample.xlsx

The spreadsheet is to be used as follows:

  • Enter client details on the data sheet, and the next due dates for each of the returns in the appropriate columns;
  • Refresh the pivot table on the Reports Due sheet, ensuring that the Include drop-down at the top is set to Y;
  • The report is sorted in ascending order by Next (which is the calculated, earliest due date for that client);
  • You can also filter the report by manager using the drop-down at the top.
  • When you have completed a return, update the next due date for that return on the data sheet. When you refresh the pivot table again it will re-sort reflecting the change.
How it works

The spreadsheet consists of a simple data sheet, applying my principles for laying out data and using protection and colour to make it obvious where data is to be entered.

There are three deadline dates in the example, but the principles can be applied to s many as you want.

There are also two calculated columns in the protected area (the protection has no password, so it can be switched off if you want to look at or edit these columns).

The first column (column I - Next Due Date) calculates the earliest of the three due dates, using the MIN function. On row 2 this reads:

=MIN(F2:H2)

As dates are stored in excel as numbers, the minimum of these three dates is also the earliest. Also note that the MIN function only applies to the numeric fields, so will ignore any cells that do not have a due date.

The second calculated column (column J - Include) is used to exclude any blank rows from the data, as the MIN function will return zero (00/01/1900 in Excel date terms) on these rows, putting them at the top of the list as they are well overdue!

This column simply uses the IF function to return an "N" if the Next Due Date is zero, otherwise a "Y".

So row 2 reads:

=IF(I2=0,"N","Y")

These two calculated columns have been copied down for 500 rows, but could easily be copied down further if you need more.

The second sheet of the workbook is a Pivot Table, whose source is all of the columns of the Data Sheet.

The pivot table has been created with the Manager and Include fields as Report Filters, and the client information as the rows. All subtotals have been switched off on the client information fields and the pivot table has been set to Classic Layout - this means that we can keep all of the client information on one row.

All of the dates have been set as Value fields, formatted as dates and set to summarise by Min. As there should only be one row per client, Sum, Average or Max would have done as well, however Min seems safer, as the point of the spreadsheet is our interest in the earliest date.

The most important change to the pivot table is the sort. By right-clicking on the Client Name field, selecting Sort -> More sort options, we can set the Client field to sort in order of the Next Due Date, ascending.

This sort is what makes the report useful, as it shows the clients with the earliest Next due date at the top of the report.

The only other changes made to the pivot table were to remove the column totals (they don't really make sense for dates), and to edit the headings for the value columns to make them more user friendly.

Obviously, this is a simple example and much more complexity could be added, however this would have confused some simple principles.

I hope you find it useful.


Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Tuesday 14 May 2013

Excel Tip: Protecting cells

One of my ten principles last week was to protect cells with formulae in them, and it occurred to me that it might be useful to do a quick post on how cell protection works.

It is really easy to use and very effective for ensuring that formulae don't get over-typed.

Protecting cells is a two-stage process:

First of all we choose which cells we want to be locked, and then we switch protection on for that worksheet.

Setting which cells are to be protected

We determine the protection status of a cell by selecting a cell, or cells, and choosing "Format Cells". This can be accessed from the right-click menu.

The final tab of the window is Protection:


By default, all cells have the Locked box ticked (as shown above). This means that the cell will be locked (and therefore can't be edited) if the sheet is protected (in the next stage).

If we untick this box, then the cell, or cells, will remain free to edit, when protection is switched on.

We will typically select the data entry cells and unlock them in this way, prior to switching protection on for the sheet.

There is also a second tick box (Hidden) that allows you to hide the formula in the cell (again, only once protection is switched on).

Switch Protection on for the worksheet

In Excel 2007 onwards select the Review ribbon and click Protect Sheet. In earlier versions of Excel, select Tools->Protection->Protect Sheet.

In both cases you will see the following:


The top tick box will always be ticked (you can't click OK if it isn't).

Under that, you can choose to enter a password that will be required to unprotect the sheet. You will be asked to enter this twice to avoid mistyping it.

Below that is a list of tick boxes where you can allow things that would otherwise not be allowed on a protected sheet. By default, the first two are ticked, meaning that you are still allowed to select any cells (whether locked or unlocked). On a data entry screen, I often untick the top box, as this stops the user even clicking in cells that can't be edited - making it easier to navigate the sheet.

Other tickboxes (further down the list) that you may want to tick, are such things as allowing the use of Autofilter, Sort, or Pivot Tables. This form gives quite a lot of control over what the user can or can't do.

When you are happy with the above, click OK. The protection settings will be applied to the active Worksheet.

To unprotect the sheet again, go to the same place as you did to protect it. You will notice that the option now says Unprotect Sheet. When you click this, you will be asked for the password (if you applied one), and then the sheet will be unprotected again.

If you then go to protect it again, the tick box options are remembered, however you will need to set the password again.

And that's cell protection!

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Tuesday 7 May 2013

Excel Tip: My Ten Principles for Excel Good Practice

As part of my role on the Institute of Chartered Accountants in England and Wales (ICAEW) Excel Community Advisory Committee, I (along with the other members) have been asked to share my personal principles for Excel good practice - and I thought you might be interested to see them too.

So here goes...

My Ten Excel Principles:

  1. Always separate out the three stages - Obtaining the data, Analysing the data, Presenting the data (OAP). This makes your spreadsheet far more flexible and easy to follow.
  2. Never include numbers in formulae - always enter the variable in a cell and refer to the cell in the formula. This makes the spreadsheet easy to follow and to update when variables change.
  3. Don't fiddle with the data - when using data from elsewhere, don't edit it, report on it in other sheets. This minimises errors and increases efficiency.
  4. Use data validation where possible, to minimise data entry errors, and to make it easier to analyse and report on the data.
  5. Always think "Is there a way I could do this that would save me time next time?"
  6. Lay out data to suit Excel - not to suit the eye. Every heading, subtotal or empty column you place among the data makes it far less useable.
  7. Get used to using the dollar signs when you refer to cells. Used well, you can write a formula once and copy it everywhere - Saving time and reducing errors.
  8. Protect cells with formulae in them - it can be very difficult to find an incorrect (or missing) formula caused by accidental typing.
  9. Use colour and formatting (as well as descriptions) to make it obvious where data entry is required.
  10. Avoid merging cells unless absolutely necessary - merged cells make a spreadsheet far harder to edit.

I'm sure I will have missed a few key ones - these are very much my personal ones - and I would love to hear yours in the comments.


Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".