8 Excel FAILS to avoid


As we go about our happy days teaching people to work smarter and faster, we see a lot of crazy workarounds.  Nothing makes us happier than the “wooooooow” moment someone realises there is a much, MUCH better way to do their work.  Here are 8 of the worst workarounds we’ve seen…

WARNING – don’t get cocky after reading the first few and think “who doesn’t know this stuff”.  It will make you feel silly if you learn something further down….

1. Inserting rows as a design feature

This makes Excel think the data is in separate lists, so it will treat blank rows differently depending on the functions you need to use.  For example, autofill will stop at a blank row, as will formulas.  So you will find your worksheet doesn’t update as you expect.

So avoid inserting blank rows or columns in a worksheet.  Instead, use formatting to emphasise key data which will help to make the worksheet easy to read and absorb.

2. Copying data instead of a link to data

Don’t copy data from an active worksheet or another cell, when you could link to it.  It creates duplication of effort when the original data change.

Instead of unnecessary copy and pasting, use the built in cell references that automatically link to the data.  So, if your sales figure is in cell D1, typing =D1 in another cell will bring that value to that cell.  Then the data will automatically update everywhere when you change it at the source.

3. Using Excel when you shouldn't be using Excel

Excel is awesome.  We love it.  But it can’t do everything.

We come across people using Excel when they should be using other software, e.g. project planning or task management software (when Excel doesn’t have enough capability).

Excel is not a relational database, but is often used as one.  Worksheets become unwieldy when used for complex project plans or to manage tasks, or to store raw data.  This kind of use is fine for manageable sized lists, but if your users have to scroll or split the worksheet, you’re likely using Excel as a database. You shouldn’t.  Get a proper tool.

4. Printing a massive spreadsheet

You would be surprised how common this is.

You press Print and wait for the entire worksheet to print, including blank rows and columns.  Instead, select the data you wish to print, then go to File > Print > Print Selection to print just that data.  It saves trees (as well as printer toner) and stops those dark looks from your colleagues for hogging the printer.

5. Applying formatting to an entire column/row

Applying formatting to an entire column/row might save you a few milliseconds, but let’s be honest – you are being a bit lazy.  It affects speed performance, can be a pain when printing and can make others wonder if there is an error i.e. did you mean to make all the rows beneath your data bright yellow!? They also might wonder if something missing or is it a formatting error?

When others have to stop and question your data, it impacts productivity and creates a lack of confidence about your work.  Format the data, not the column/row.

6. Entering free text for known variables

Typing free text may seem quicker at the time and autofill tries to help you apply consistency, but it leaves things open for different user’s typing styles.

Use data validation to ensure users select the correct text label, e.g. company name. Go to Data > Data validation and set criteria that ensures users can only select the correct data.


7. Using the colour red

Using Red as an emphasis colour (in cases where you want the data to stand out) is a bad idea.

Colour has different connotations to different people, red typically means negative and can send the accountant in a spin! Also, your emphasis is lost the minute someone prints the worksheet in greyscale.  Instead, use tables, bold or boxes to emphasise data points.


Merged Cells - the ultimate torture

Merged cells should be banned under the Geneva Convention.  They are troublesome for sorting and filtering data and you can’t fill formulas down over a merged cell and inserting columns/rows can be troublesome.

Often used to improve design of a spreadsheet, you are far better off using Format Cells > Alignment > Center across selection.  It performs the same task if you’re looking to improve the look of your data headers.  But it won’t drive you and anyone else using that spreadsheet crazy with a capital K.

People literally love our training

Because we change peoples lives.  No exaggeration.

We train at your offices, even at your desks.  We make it super practical so you can apply it immediately.

But the real magic in what we do is helping you change the way you work.  We help you make the most of IT that you already own – especially the powerful tools in Office 365.

Stop working like you did in 1999.

Find out how by getting in touch.

[contact-form-7 id=”800″ title=”Contact form 1″]