Formatting

Merge and Center warning! Formatting issue By Neale Blackwood Ever had an error message which stated that you couldn't do something to a merged cell? Well the merge format is the culprit. The Merge and Center tool on the toolbar is commonly used to format headings to make them look good by centering a [...]

Drawing perfectly horizontal or vertical lines Shift key By Neale Blackwood Have you ever tried to draw a perfectly horizontal or vertical line using Excel's drawing tools? It can be done, but it is fiddly and frustrating. Well, next time try holding the Shift key down whilst you're dragging the line and see how [...]

Link Text Box contents to cell contents Text box feature By Neale Blackwood Text boxes are useful because you can easily place them anywhere on your spreadsheet. You can link the contents of a text box to a cell. First draw a text box on your sheet. Then when the text box is selected [...]

Hiding sheet gridlines Display Feature By Neale Blackwood When you are designing a spreadsheet for others it can be useful to turn off the gridlines on some sheets to make them look more like pages and less like spreadsheets. You can then use colours and borders to emphasise the areas where the user should [...]

Color code your sheet tabs Built-in feature By Neale Blackwood Colour is a powerful communicator. Effective use of colour in your spreadsheets can make them easier to use and understand. One of the ways to use it is to colour code your sheet tabs. Eg you might have sheets for instructions, workings, data, input [...]

Wrapping Text Macro solution By Neale Blackwood The Wrap Text format is often used to reduce the width of a column and still allow the contents to be displayed, as it creates line breaks with a cell. Excel has an icon in the Home Ribbon in the Alignment Section to Wrap Text. The macro [...]

Display Fraction Custom entry By Neale Blackwood Ever needed to display a fraction in a cell and have it used in calculations? Well you can achieve that fairly easily by entering a zero and a space before entering the fraction. Entering "0 3/4" in a cell will display 3/4 and use 0.75 in calculations. [...]

Templates I use a lot of custom formats and custom colours in my spreadsheets. I have to re-create them in my new files. Is there a way to have these custom formats automatically included in new sheets and files? By Neale Blackwood Yes, by using templates. Excel allows you to create templates that have specific uses. [...]

Cell Styles Excel 2007 Cell Styles By Neale Blackwood The Cell Styles button in the Home ribbon tab in the Styles section offers you an easy way to standardise your Excel formatting. When developing Excel sheets it is best practice to use the same formats for certain cells to make it easier for users [...]

Finding Formats Excel 2007 feature By Neale Blackwood Excel 2007 has a new feature that allows you to find formats. To find a cell(s) with a certain format you use the normal Find system which has had format functionality added to it. Ctrl + f will open the Find dialog. It is also on [...]

Thousands format Display values as thousands By Neale Blackwood If you need to display numbers as thousands in a report you don't need to divide all the numbers by 1000, you can apply a Custom format to the cells and have Excel display the values as thousands. The underlying values are unchanged but they [...]