Don't you get annoyed when you see an ill-formatted excel worksheet? Inconsistent fonts and font sizes, alignment, and other non-uniform formatting is not something anyone likes to see in excel sheets. Who doesn't like to see worksheets which are properly and consistently formatted? Professional-looking worksheets are not only easy on the eyes but also helps easy understanding and save time figuring out the significance of different column heads and other data.
Having worked in a multi-national consultancy firm, I know how important it is to present the Excel files you work on in a properly formatted manner to your seniors and clients. It makes things easier for them and is highly appreciated. On the other hand, a file having inconsistent font sizes, color highlights, and alignment and really prove to be a big turn off for anyone viewing the file.
In this article, I'd be discussing the principal areas which one should be keeping in mind when formatting a spreadsheet and finalizing it for presentation. I'd be doing this with the help of a case study.I'd like you to download the example file which I'd be using for explanations.
If you download the file, you'll see that it has two sheets. The first sheet is the raw data that we need to format to make it look more presentable. Basically, the result that I'd be aiming for is given in Sheet 2. You can see the difference I'm sure! The first sheet looks totally unprofessional and it looks that the same has been worked upon by an amateur. The second sheet, on the other hand, looks much more presentable. I've taken a very small and simple example data on purpose, as it will help you understand my point easily.
Download the example file by clicking here.
Please note that the formatting options that I suggest below are based on my personal preferences. I suggest you experiment with different options (fonts, font sizes, colors etc.) and come up with something that suits you and your organization.
Open the first sheet in the example file and follow the steps given below:
Step #1: I like to have the first row and first column of an excel file as blank. Insert a blank row above the first row and a blank column before of the first column. To insert a blank row, select the first row and press Ctrl Shift +. You can also right-click on any cell in the first row > Select 'Insert…' > Select 'Entire Row' and click OK. You can insert blank column by following a similar method.
Next, you'll need to adjust the height and width of the first row and first column you just inserted to 5 and 0.5 respectively. You can do this by selecting the row (Row 1 in our case) > right-clicking on any cell > selecting 'Row Height…' > setting the row height to 5 and clicking OK. You can set the column width by following a similar procedure.
Step #2: Next, you'll need to adjust the alignment depending on the type of data contained in the respective columns. You can see that the alignment in the sheet is horribly inconsistent. Let's fix this. As per the general standards, numbers and dates should be aligned to the right, and text should be aligned to the left. So, make columns B, E and F aligned to the right and the other columns aligned to the left. To do this you just need to select the respective columns and press keyboard shortcuts Alt H A L and Alt H A R to align to the left and to the right respectively.
Step #3: You must have noted by now that you cannot see all the text contained in the Product column. Also, the column width of the Category column is unnecessarily high. You'll need to adjust the column widths such that all the text is clearly visible, but make sure the width is not too high either. You can easily do this by selecting the whole data and press the keyboard shortcut Alt O C A.
Step #4: Next, select the table and make its background color as White. This makes the grid-lines invisible in the table and makes the data look better. You can do this by pressing keyboard shortcut Alt H H and then select the white color.
Step #5: Now, select the table and change its font to Calibri. Calibri is the font that I prefer when aiming for a professional-looking spreadsheet. I suggest you try a few fonts and chose the one that you think looks suitable. Avoid flashy fonts (like Comic Sans) though. The option to select the Font is available on the Home tab of the main menu.
Step #6: Next, select the table and change the font size to 11. Again, this is a personal preference. 11 is a font size that I find most appropriate. Being not too small nor too big, I find it easy on the eyes. The option to select the Font Size is available in the Home tab of the main menu.
Step #7: Now, let's format the column heads of the data table. First, select the column headings and press Ctrl B to make them bold. This makes them stand out from the rest of the data.
Step #8: Also, change the font color of the column heads to dark blue. Dark blue is a standard color used by many organizations in their worksheets to make the headings stand out, and I totally agree with them. The option to select the Font Color is available in the Home tab of the main menu.
Step #9: Next, select the heads and apply a dark blue (same as their font color) border under them. For this, you can press keyboard shortcut Ctrl H B to open the Borders panel and then select the Bottom Border option. To change the color of the border, you should select the column heads > press keyboard shortcut Ctrl 1 to open the Format dialogue box > go to Border tab > select the Border > chose the appropriate color > press OK.
Step #10: You must have noticed by now that the number formatting is not consistent in the Amount column. Some amounts have a decimal point and 2 digits after the decimal point. Ideally, amounts should have 2 digits after the decimal point. Even if the amount is a whole number, it should have two zeros to make them consistent and presentable. Also, the amounts should have a thousand separator enabled i.e. the amounts should have comas after every set of 3 digits staring from the right.
To apply such formatting, select the numbers (amounts) > press Ctrl 1 to open the Format dialogue box > in the Number tab, go to Number option > set 2 in the Decimal Places > tick Thousand Separator option > click OK.
Step #11: Lastly, you'll need to make the inconsistent formatting of the Date column consistent. Select the cells having dates > open the Format dialogue box > in the Number tab, go to Date option > select the first option in Type > Click OK.
Now, your worksheet should look like the one given in Sheet 2.
After going through the above exercise, you must have got an idea about how important the look of your worksheets is, why you need to make them look professional and how you can do that.
What other formatting choices you prefer to make when you work in your Excel worksheets? I'd love to know about them. Please leave a comment and me about them.
See in soon with another set of Excel tips. Till then. Happy Excelling!
The author also blogs at MadAboutExcel.com and can be reached at vikram@madaboutexcel.com
CAclubindia
