Many (thousands of) Rows
We often analyse files containing many hundreds or thousands of rows, with multiple columns of data. With this amount of data it can be difficult to make sense of the information contained within the file.
The first thing we usually do is to reduce the magnification so that we can fit as much information as possible on to the screen, but this can add to the problem of visualising the data. All is not lost and there are a couple of simple, yet very effective, things we can do to alter the appearance of data and make it much clearer to manage and visualise.
The two screen shots below, even with the small magnification, show how much easier it is to work with large amounts of data when each alternate row is highlighted in a different colour.
You could manually highlight each row and fill it with colour where there you only have a few rows of data.
However, where there are more than a few rows, say 20, this would be very time consuming and prone to error. Therefore, this process can be performed with conditional formatting and the use of tables.
Conditional Formatting
Firstly highlight the cells that you wish to format. If you want to format the whole worksheet click the box above Row 1 and to the left of column A to highlight everything.
Then from the home tab select Conditional Formatting and then click New Rule. In the Select a Rule Type box click on Use a formula to determine which cells to format and enter the following formula in to the Format values where this formula is true box=MOD(ROW(),2)=0.
Finally select the formatting you require from the Format box. This could be text as bold or a different colour or as in this example the row is filled with a different colour. You can select a combination of formatting requirements if you wish too.
Then click OK and or Apply.
Tables
Another way to quickly add shading to alternate rows is by converting your data to a Table.
This is useful when you want the additional benefits that you get with a Table such as filtering and formatting being automatically applied to new rows added in to the Table.
Just select Format as Table from the Home tab and then select one of the predefined layouts.
With these two simple approaches large data-sets are much more manageable to read, review and analyse.
We will return to both conditional formatting and tables and review them in detail in future posts.
