In part five of this series we look at how you can ensure your spreadsheets look great and display data the way you want.
Formatting your spreadsheet is vital to ensuring that data is displayed clearly and that information stands out where necessary. There are many different aspects you can format to achieve this. Below we’ll look at a few examples.
In the following video, we explore how to adjust the width of columns in our worksheet.
Borders and shading
Borders and shading can be used to highlight information and make it distinct from the surrounding data.
It’s important to use an appropriate font type and size for the task at hand.
It would be very repetitive to have to go through the same steps on every cell you’d like to format in your spreadsheet. One solution is to use the Format Painter which takes the formatting from the currently selected cell and applies that same formatting to another cell or set of cells.
It’s probably the BEST formatting shortcut you will ever learn, and it can be used across the whole MS Office suite:
Fill handle and flash fill
If Format Painter hasn’t blown you away, then these two top tools will!
The fill handle and flash fill are both tools which act as shortcuts to completing rows and columns of data. This is useful when you have a formula or pattern which you want to apply as it saves you from having to copy and paste multiple times. The fill handle can also be used to number rows or columns of a table.
The fill handle appears at the bottom right-hand corner of a selection of cells:
This small square at the bottom right corner of a selection can be used to extend the contents of the selected cells to new cells, with Excel using the content of the selected cells to guess the content you require in the new cells.
This video shows the use of fill handle with more than one column:
In the following video, we are going to explore how to use Flash Fill to complete rows and columns of data:
Time and date formatting
Depending on what data is being used for, or what your personal preference is, it is useful to be able to display dates and times in a variety of formats. Luckily, Excel provides a wide range of built-in formats and allows you to create your own, so you can display dates and times whichever way suits you without having to type them out each time.
Excel includes a wide range of different date and time formats. The built-in formats are shown with an asterisk depend on the global Windows regional settings in the Windows Control Panel:
Alternatively, you can specify your own format using the Custom option. The available codes are as follows for dates:
Most of these are self-explanatory but note that the use of one letter (d,m) will not include a leading zero, whereas using two (dd,mm) will. Four ds or ms give the day or month name in full and five ms just give the first letter of the month name, which might be useful if you have 12 very narrow month columns.
This is the equivalent for times:
This time we’ll pick out the [hh] format. If you just format a time, or date with a time, using h or hh as part of the custom format, then it will only display hours in excess of multiples of 24 (i.e. only the hour element, ignoring days). Using the square brackets forces Excel to display the full numbers of hours including the days.
You can also format a date within a text string in Excel using the TEXT() function and the above custom formats, for example:
=TEXT(A2,”dddd, dd mmmm yyyy”)
Now you know about formatting, there’s no excuse not to have incredible looking spreadsheets.
Look out for part six where we are looking at THE ESSENTIAL formulas that you need to know in Excel. It’s a lesson you won’t want to miss!
AAT students and professional members can access a wide range of Excel resources and training.
Filtered provides algorithmically personalised Excel courses written by subject matter expert.