Top Excel Tips

Excel is one of those applications that so many of us have on our laptop and desktop PCs. Most of us realise that it’s an amazingly powerful piece of software. However, most people I speak to tell me that they think it could do so much more for them but they don’t know how.

Well, in a short blog post I can’t turn someone into an Excel Guru but I can let you share a few tips that I’ve found useful and have saved me time. Hopefully you’ll find them useful too.

Keyboard Shortcuts

Some people are happy to use their mouse, drop down menus and ribbon items for everything. Others find that keyboard shortcuts suit them better. I use a little of each so here is a short list of keyboard shortcuts that I find useful;

• To highlight a whole column highlight any cell in that column then Ctrl and Spacebar
• To highlight a whole row highlight any cell in that row then Shift and Spacebar
• To highlight the whole worksheet Control and A – this can be useful for changing the font name or size of all you cells or perhaps to choose an alignment other than the standard one ( I personally don’t like the default vertical alignment to the bottom of cells, I prefer top or centre)
• Spell check the whole sheet – F7
• To show the formulas in cells instead of the results of those formula Ctrl- and ` key

Appearance

Doing a little work on the appearance of your worksheet can make the important information stand out to you while working on your data. Of course we all have different personal preferences but here are a few things that I often do;

• Formatting

The Excel default is for text to appear in the bottom of a cell. We don’t notice this if the cell is fairly narrow but if some cells in a row need to be quite deep then those with only one line will show that line at the bottom – as I said earlier this is personal preference but I like to see those single lines at the top of the cell. So I select the whole worksheet which can be done by either clicking the Control key and the “A” key at the same time or click on the little box above the row numbers and to the left of the Column letters. Once I’ve done this I use the format. My preference is to click the right mouse button and from the drop down menu click on “Format Cells”, select the “Alignment” tab and under “Vertical” select “Top” then “OK”.

• Column Width

Once I have data in my worksheet I often find that the various columns having different types of information in them and would benefit from being different widths. Once again I select the whole sheet as described above then hover the mouse over one of the lines between column names and double click. Excel will automatically reset the width of all your columns to suit their contents.

• Autocomplete

One massive timesaver is the facility of Excel to autocomplete. Say you wanted each row of your spreadsheet to represent a different date so you may want to show the date in Column A. A good way of saving yourself time would be to write a date (e.g. 20/2/2016) in Row 2, the next date (21/2/2016) in Column A row 3, highlight the two cells then move your mouse pointer to the bottom right of the bottom of the two cells. The mouse pointer should change from being a thick cross to being a line cross, then hold down your left mouse button and drag the pointer down your column as far as you want to go. Excel will identify your pattern and keep adding to it. As it recognises that your cells contain dates it will add to the date for each row taking account of month changes and leap years.
The autocomplete facility works for all sort of information. If Excel can identify the pattern it will add to it but, keep your eye on it, it doesn’t always identify your pattern correctly.

So there you are, a small handful of tips. I hope that you find them useful.

By Mark Kendall

Sussex Data Solutions

We often publish third party posts which may be interesting or relevant, thank you to Mark Kendall for the excel tips. If you have any comments please add them above or contact us direct.