If you want to know how to make spreadsheets look good the first thing you'll need to know is how to format an Excel spreadsheet. In fact to format a spreadsheet is the technical word used to describe how to make a spreadsheet look good.
The fundamentals of an accounting Excel spreadsheet is knowing basic spreadsheet formatting. This would include knowing how to change cells to currency in Excel, changing cell colour, and the rest of the standard formatting features within Microsoft Excel.
This tutorial is one of a series of Accounts Tutorials that you are encouraged to follow. As a result you will learn everything from creating a basic accounts worksheet to a Pivot Table. Also, you will learn many everday Excel hints and tips.
If you are wanting to download then Excel Accounts Template that you will have created by the end of the tutorial, you are more that welcome to use the accounting template. However, please spend at least a few minutes watching the accounts tutorial, you'll get so much more out of it.
Also if watching an Excel accounts tutorial isn't for you and you would rather have step by step instructions then keep reading.
To start making your accounts look pretty in Excel the first thing to do would be to create a title. Knowing how to insert a row in Excel can help. Know it's true we covered that in the previous accounting tutorial in the series but just in case here's how to insert a row in an Excel spreadsheet.
Notice that whenever you insert a row Excel always adds the row above the row you selected. To insert multiple rows in Excel simply drag down the number of rows you would like to insert. Then right click and click on Insert Row. Excel will insert as many rows as you selected.
Did you know that there's an Excel insert row shortcut? Try the following:
When you merge and centre cells you bring together a group of cells into one. Knowing how to merge multiple cells into one cell in Excel answers the question how do you add a title to an Excel spreadsheet? Here's how you do it:
Now cells A1 through to E1 are merged into one cell. Notice that when you select the newly merged cell, it is given the cell reference A1. If you had data in any cell other that A1 you would lose the information. Excel will only keep any data you already had entered in cell A1.
You can change the colour of cells in Excel. This not only makes the spreadsheet look pretty, it also makes your spreadsheet easy to read. Let's change the colour of our accounts spreadsheet:
Now you can see that the background colour of cell A1 has changed. If you want to ensure the colours in your Excel spreadsheet match your company colours:
You can change the background colour of cells in Excel by selecting the cells before you change the background. Why not try to change the background colour of cells A5 to E5.
When you know how to change the font in the Excel Sheet you can really bring your accounts to life. In understanding how to do this you will first need to understand that the font will be changed for whatever cells are currently selected when you change the font in Excel. Here's how:
Note that as you move your mouse over the fonts the title updates to show you what the merged and centered title would look like in that particular font. Another tip is that you can search for a font you already know the name of by typing the font. I've chosen Tahoma for the Title and the column headings.
To change the font size in an Excel spreadsheet you would select the cells as described above. You would then use the Font Size drop down list to change the font size to the size you want.
You can also use the font size increase and decrease buttons if you just want to experiment. You don't have to accept the font sizes in the font size list. You can type any value you want into the font size box, even decimal numbers. So if you need your font to be 19.5 that's no problem. Simply type in the number you need.
You probably already know how to change the width of one column in Excel. You might even know that you can change the width of a column automatically by double clicking on the line between the columns in the column headings. But did you know how to change column width in Excel for multiple columns? Well if you didn't here you go:
If you want to know how to make columns the same size in Excel then you can do this:
Hey presto! Your columns are the same width
The quickest way to add borders to a worksheet is drawing lines in Excel. That's right you should know how to draw a border in Excel. Now how do you apply borders in Excel:
If you want to know how to remove lines in Excel then you at first need to know which lines. If you want to remove the grid lines you can do so by going to View on the Ribbon and unticking the Grid lines checkbox.
If, however, you wish to erase lines in Excel that you have drawn do the following:
Knowing how to freeze panes in Excel means that you can fix rows or columns. Then you can scroll down the spreadsheet and still see the column headers. Try the following:
The Format painter button copies the format or look of currently selected cells. You can then select the cell or cells you wish to copy the formatting to. This is handy for one cell but absolutely fantastic for multiple cells. This is because you can use the format painter to create alternate coloured rows in Excel.
Now your spreadsheet has alternate grey and white rows. See how handy knowing how you should use Format Painter is.
Don't have time to follow the above tutorial? Here you go! This is the completed file: Simple Formatted Accounts Template.