Fixing errors in data sets

By Neil Shorney, Director, Naturally Sales Ltd.


In large data sets,  particularly those downloaded from an external database, it's common to find errors which need tidying. Often these errors aren't errors at all, but simply the way Excel displays numbers within the spreadsheet layout. Today, we'll look at 2 such "errors" which have come up in real examples from recent students on our classes.


Error 1: the hash-tag error: ########
This is a common sight in downloaded spreadsheet data, and one which is easily dealt with. The problem here is not an error, but simply a number (including dates, currencies, percentages, etc.) which is too big for the cell. To view the cell content correctly, resize the column by double-clicking the mouse in the column headers between the one you're in and the one after (i.e. if your error is in column C, double-click between C and D) and the column will expand to fit the longest data.

Error 2: the E error: 5.35E+08
Another common sight, this "error" particularly occurs where you have a long number in a cell with more than 6 characters. It does not occur on dates or currencies, and the cause is the same as the previous example - the number is too big for the cell, yet Excel is attempting to give you a little more information about the number, showing how long the number is (after the +) and the first few digits (535). Much of the time, the method described in section 1 (above) will also work here. Sometimes, however, an extra step may be required. To display properly, the cells should be formatted as "numbers", whereas the default is "general". Before resizing the column, highlight the cells, right-click and format as a number (you may need to adjust the number of decimal places).

Spreadsheet art

I discovered this fabulous chap recently, who uses Microsoft Excel to create works of art. I struggle to find a business use for this, but it's certainly an original application of spreadsheet skills. Take a look at this web page.

Saving your workspace

By Neil Shorney, Director, Naturally Sales Ltd.

You're working with a number of different spreadsheets, you've taken the time to arrange them within the Excel window, everything's where you need it, and it's time to go home. There's no need to spend time re-arranging everything again tomorrow morning – Excel's handy “save workspace” functionality allows you to save Excel exactly as you see it now, and open it up again whenever you need to.

The “save workspace” button is on the View tab. This will create a new file which is effectively a record of how you have Excel set up at the time. It doesn't contain your actual spreadsheets – It contains their size and location information, for re-assembly when it's convenient for you.

Presenting Data

By Neil Shorney, Director, Naturally Sales Ltd.

Pivot tables are a great way to present complex data in a flexible way. Select your data, then click Insert-Pivot table. You can then build your pivot table quickly and easily. For more permanent and nicely-presented reports, such as a dynamic dashboard, it's better to use formulas to interpret your data which will update as new data is added.

We look at this in more detail in Advanced Microsoft Excel. Alternatively, we can customise training to your needs and include your own spreadsheets as exercises.

Formatting as a table

By Neil Shorney, Director, Naturally Sales Ltd.

In Excel level 1, we learn about using the Format as table option to improve the look of data. But did you know that this can also be a time-saver?

Tables provide a quick-and-easy way to total lists, add rows, filter and affect the look-and-feel of your data. To create a table from existing data, highlight your data and choose "Format as table" from the Home tab, or "Insert table" from the Insert tab. As you create the table, tick the "My table has headers" box to use the top line as column headings. You'll see that the look of your data will change and a contextual menu Design tab will appear on the ribbon. In here you have a number of options, for example, adding a quick "Total" row at the bottom, changing the format of the first or last column to bold, removing duplicates, and other useful tools. In the "Table styles" box, you can quickly change the colours of the table. You'll also notice that Autofilter arrows will appear at the top of each column.

One of the most useful aspects is that if you have a column of formulae in your table, then adding a row in the middle will automatically copy those formulae to the new row, saving time copying and pasting.

Should you at some point decide that a table is not the best format for your data, the "Convert to range" button in the "Tools" box will turn it back to a normal data range.

Preserving formatting when filling down

By Neil Shorney, Director, Naturally Sales Ltd.

Today we're going to take a look at Excel's fill down function, and what that means for formatting.
The fill down function is one of the greatest time-savers in Excel. Create a formula with relative references, drag the formula down and watch your data complete itself automatically! It's a great tool, until formatting gets in the way and spoils the fun.

For those unfamiliar with the tool, the idea is that you select the cell with the formula to copy, click on the little black square in the bottom-right corner, and drag it down to create further formulas automatically. You can try it in the attached example.

A frustrating problem is that doing this also copies formatting, so a nicely-formatted table as in the attached loses its colours and you need to spend time re-formatting.

The solution is to use the right mouse button instead of the left. As you let go after dragging, you'll be presented with a number of options. The one you'll need is "fill without formatting" - the formula or data will be copied as before, but formatting will remain in place.

Paste values

By Neil Shorney, Director, Naturally Sales Ltd.

A useful data manipulation technique in Excel is "Paste Values". Copying formula results and pasting only the values elsewhere allows for two things:

  1. to move the results around without them changing
  2. to preserve the results at a moment in time before then changing data

The method differs between versions of Excel. In 2003 and 2007, you need to right-click, choose "Paste Special", select "Values" and click "OK". In Excel 2010 the process is much smoother – simply right-click, then click the button with "123" written on it.

For more about data manipulation, see the Intermediate Microsoft Excel course.

Wrapping text for flexible cell layout

By Neil Shorney

In Excel level 1, we look at techniques to re-size columns in large database downloads to make the data easier to read. One of these techniques is wrapping text. Perhaps you have a large amount of data in a cell, for example a postal address. In your data set, you want to be able to see the full content of the cell, but you don't want a column so wide that it becomes unmanageable. The solution to this problem is the wrap text function.

To wrap text in a column, highlight the full column then right-click in the selected cells and choose Format cells. On the second tab (Alignment), tick the Wrap text box then press OK. Instead of the column re-sizing to accommodate the data, the rows will increase their height and the text will be split over several lines within the column width you need.

We look at more cell formatting in Excel level 1.