Excel Tip: Don't hold your data in Excel if you've got somewhere better!

Just a short post this week, and a one you might be surprised by. I am going to encourage you not to use Excel!

Yes, after years of telling you how to get the best out of Excel, I am going to tell you to avoid using it!

Well at least for one particular purpose...

Whereas a lot of my work is producing spreadsheets that are self-contained, I work with many clients where I use Excel as a tool to report from their database systems (typically accounting packages or ERP/MRP systems).

Many times, these reports are required because the system won't give them what they want - some of the time because all of the data they need isn't stored in the system.

In this scenario, it is very easy to store the additional data in the spreadsheet report, but I would urge you - DON'T!

Whereas the report will work just as well, if the additional data is stored in the spreadsheet, the information will not be available from within the system, or for other reports required in future. Very quickly, you can find  various different versions of the same data scattered around in spreadsheets all over the business.

If at all possible, find a way to start recording this data in the system - and use Excel to report on it like the rest of the data.

This is usually possible, although inevitably there are some systems that are not flexible enough for the company's true needs. Storing the data in a spreadsheet outside of the system will work as a patch, but is not the ideal solution.

Excel is a fantastic tool that has many applications - but it is important to know when not to use it too!

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".

Excel Tip: Don't fiddle with the data!

Yesterday I had a conversation with a client that I have had, in various forms, many times before.

It relates to using information in a spreadsheet from an external source.

Almost every time I find a client using data from their accounting system, or provided by a supplier, customer or other third party, they approach it in a way that significantly multiplies the work involved and increases the risk of errors.

It's a shame because all that is required is a different approach, that rarely requires greater Excel skills than they already have.

In most cases the information is received as a spreadsheet - either generated from another software package, or emailed from a third party. Typically this information then needs to be edited and re-formatted to produce the desired end product - which might also have some pivot tables or charts based on the data.

The usual response to this situation is to do just that - take the spreadsheet that has been received and then edit it until it look s how they want it.

Unless this is a truly one-off situation, it is almost certainly the worst route to take, as you will need to do all of this editing again when you receive the next version of the data, next month, next week, or tomorrow!

Applying a little thinking to the situation suggests a much more efficient approach.

You will always receive this data in the same format, and will always require the same output format, so why not use this knowledge.

DON'T EDIT THE SPREADSHEET YOU RECEIVE!

Instead of editing the spreadsheet you receive, paste it into a blank sheet in a new workbook and create the output you want on the other sheets referring to the sheet with the data on it, but not editing the data itself at all.

Doing it this way, means that next month/week/day you can just paste the new data in and your job is done!

You can spend your time getting it right once - reducing the risk of errors - and remove all of the work in future.

Incorrect spreadsheets have hit the headlines in the US this week, with Excel errors discovered in the calculations behind a report by Harvard Economists Reinhart and Rogoff that has been used by Republicans to support budget cuts. Another timely reminder of the potential consequences of not getting these things right.

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".

Excel Tip: Do you really need to merge those cells?

Just a short one this week, along the lines of my cautionary post a couple of weeks ago on the dangers of cut and paste.

When working on a spreadsheet that someone else has built, there are a number of things that can make life particularly difficult. One of these, which is rarely necessary, is the use of merged cells.

Merging cells allow you to combine one or more adjacent cells into one, usually to allow the cell contents (text or numbers) to be centred, either horizontally or vertically, in the larger combined cell. This is purely a presentation tool, which you can use to make your spreadsheet look better.

To access Merge Cells, you can select the range you want to merge, then either choose format cells and tick the box on the Alignment tab, or use the drop-down in the middle of the ribbon on the more recent versions of Excel.

The purpose of today's post, however, is not to teach you how to use Merge Cells, but to warn of the dangers of using them unnecessarily, and in the wrong place.

Merging cells in a table in Microsoft Word is great and gives you far greater flexibility in your layout, however Excel has one fundamental difference to a table in Word - these cells contain data and/or calculations, linking them to other cells in the worksheet/workbook. Excel treats the contents of the merged cell as being contained in the top left cell of the merged range.

Regular readers will know of my OAP approach, which breaks your Excel spreadsheet design down into three distinct functions:

• O - Obtain the data
• A - Analyse the data
• P - Present the results

If your spreadsheet follows this approach, then there won't be too many problems using Merge Cells at the P stage, as this worksheet (or area of a worksheet) will simply be presenting data obtained and analysed elsewhere -and will not be being used as data itself.

Unfortunately, most spreadsheets do not follow this approach and each worksheet tends to include all of these steps.

Now, when making changes to a spreadsheet that includes merged cells, among the data and analysis, we are faced with all sorts of complications, including the following:

• Data containing merged cells can not be treated like a normal data table - meaning that we can't use all of the tools that we might want to use for referring to a properly formatted data table, such as pivot tables, SUMIF, etc;
• Copying and pasting ranges is restricted to those with cells merged in the same way;
• Fill down doesn't work if any of the cells in the range to be filled are merged;
• Even if we unmerge all of the cells, this rarely solves the problem, as this action will assume that the merged cell contents should be placed in the top left cell of the unmerged range - which may not be where you want them to be. Also, having done this, it is often not clear where your data is, as the labels may now be in a completely different place.

These problems can cause huge amounts of extra work when working with a spreadsheet like this. My advice would be to only use merged cells in a sheet that you know is purely for presentation, or that is so simple that it is very easy to see what is going on.

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".

Online Excel Training - Exclusive Reader Offer

Regular readers will know that I have been working on ways to help you with more in-depth Excel Training than blog posts will allow.

You can already purchase my Introduction to Pivot Tables course and I do have plans for further training videos in the future, however your feedback in response to the survey I did back in March suggested an alternative route might be a better way to ensure that all of your needs are satisfied.

It became quite clear from the survey that:

• Your needs are diverse, and everyone has different gaps in their knowledge;
• A combination of video and written content was desirable;
• To try to cover all of your requirements would be too much to do on my own.

Fortunately, I have since spent some time talking to an excellent organisation that already has these areas covered and it struck me that I wouldn't really be providing you the best service to try to re-invent the wheel.

Excel With Business offer a 32 module Excel Training programme that covers everything you might want to know about Excel (and then some). What's more, and this is the really neat trick, they provide a diagnostic test to determine your proficiency, so that you can focus on the areas that need attention - making the service ideal for beginners and experts alike.

If that doesn't already tick all of your boxes, I have negotiated a deal with Excel With Business to offer you the tests and a full year's access to all of the modules for 50% off the usual price. For full details, click here.

It is only fair that I make it clear that I will receive a commission on these sales, however I would be recommending this course to you if I didn't - and the extra cash helps me to continue providing the blog content free of charge!

I believe that this course can really transform your use of Excel and I would strongly urge you to take a look at what is available.

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".

Excel Tip: Beware - Cut and Paste IS NOT the same as Copy and Paste

I hope those who celebrate it have enjoyed the Easter break.

I just have a short, but very important, post this week as we ease back into work.

This is a warning about the very significant difference between "cut and paste" (or "drag and drop") and "copy and paste", when it comes to Excel.

In most other applications, e.g. Word, the difference is negligible. Cutting or dragging moves the information (removing it from its old position) and copying does the same thing but leaves the original information in place too.

There is a far greater difference between these two methods in Excel and the implications of not understanding this difference can cause havoc with your spreadsheets.

The reason for the difference is the potential interconnected nature of the information being copied or moved in Excel. The cell being copied or moved may have formulae referring to it elsewhere in the spreadsheet, and/or may refer to other cells itself - a scenario that does not occur in most other applications.

When this is the case, Excel treats cutting and pasting very differently to copying and pasting.

Let us look at Copying and Pasting first as this is the simpler one. This has no impact on any cells referring to the copied cell. They still point at the original copy. However, when the copy is pasted elsewhere in the sheet, any cell references are treated as relative to the cell's position. - so if the original formula referred to the cell above it, the copied formula would refer to the cell above the its new position. This can be overridden by using the dollar sign in the original formula.

Cutting and pasting, which is exactly the same as dragging and dropping, works very differently. This time, any cells referring to the moved cell are amended to refer to the cell in its new position. Also, all cell references to other cells are treated as absolute (whether or not the dollar signs are used).

Here is a simple table for ease of reference:

	Copy and Paste	Cut and Paste (or Drag and Drop)
References to other cells	Treated as relative unless dollar sign is used on the original reference, as this forces Excel to treat the reference as absolute.	All references treated as absolute.
Cells referring to the moved cell	No change. These still refer to the original location of the cell.	All formulae referencing the moved cell are amended to refer to the cell in its new position.

One of the biggest risks caused by not understanding this difference is users cutting and pasting, or dragging data within a data entry table, when formulae are looking along the row. For example, if a user drags the data in row 3 down to row 4 (maybe to make room to enter some new data in row 3), then all of the formulae referring to row 3 will now refer to row 4 (as will the formulae on row 4 that already refer to that row), however none of the formulae will refer to row 3.

Unfortunately, Excel does not provide an option to disable cut and paste, so users must be encouraged to always copy and paste instead (the contents of the original cells can then be deleted).

Be careful out there!

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".