Under the sheets - part 2

Following last month's Excel starter guide, the Windows team gives you the lowdown on cell protection, conditional formatting and much more...

  • E-Mail
By  Matthew Wade Published  October 5, 2006

|~|Excel-part-two---image-F.jpg|~|Right-click on the cell to the right of the Cash field, under ‘Current Assets’ (cell D11). You'll notice that the cell has been formatted to Currency instead of General.|~|In part one of Under the sheets, we showed you how to make your very own budget sheet from scratch using Microsoft Excel. This month however we give you wider scope by introducing you to Microsoft’s Office range of useful templates (available here). Here you can find a wide variety of free Excel templates, covering everything from timesheets and financial statements to detailed sales reports. In this month’s guide, we’ll use Microsoft’s Balance sheet template; to explain how to format cells, add conditional formatting, protect your spreadsheets and much more. Task 1: Make the template your own On opening the balance sheet template you’ll notice that the colour scheme is blue, white and black. To change the colour of the text, simply highlight the text and click the ‘Font Color’ button located to the right of the ‘No Fill’ button. To change the colour of the whole table, select all the cells in the table and repeat the same process. We recommend adding a different colour to cells with totals in them to make these stand out. Also remember, use light colours for a cell’s background and darker colours for numbers or text to make the data easy to read (see pic). For more design tips, check out our ‘Spreadsheet design rules’ at the end of this article. Task 2: Format cells Now it’s time to format certain cells. For example, if you right-click on the ‘[Date]’ cell (i.e. Cell C3), and click ‘Format Cells’, you’ll see that ‘14-Mar-98’ has been selected under the Date’s ‘Type’ field. If you’d rather use a date/month/year format, simply navigate up to ‘3/14/2001’ in the list and hit OK. Next, right-click on the cell to the right of the Cash field, under ‘Current Assets’ (i.e. cell D11). Notice that the cell has been formatted to Currency instead of General. Currency formats are best for general monetary values. Under the Symbol column, the US dollar sign has been selected. If you want to change this to another currency, such as the British pound or the Euro, click on the arrow next to the dollar sign and choose your desired currency from the drop-down list. If you want cells in your sheet to support decimal places, navigate to the ‘Decimal Places’ field above ‘Symbol’ and type in the number of decimal places you want, such as 1, 2 or 3. Next, use the fill handle (detailed in last issue) and drag your new cell formatting to the ‘Current Asset’ cells below. To add this formatting to cells under Current Liabilities, Fixed Assets and so on, simply click on the cell D11 and click on the ‘Format Painter’ button on Excel’s toolbar. Next, click on Cell H11 and simultaneously drag the pointer downwards until your reach cell H16. (Note: while dragging your mouse pointer you'll notice a small painter brush appear). The formatting from D11 will now be applied to all these cells. Repeat this process for Fixed Assets, Long-term liabilities and so forth. Apply these formats to the totals as well. Task 3: Use conditional formatting If you wish to highlight certain figures that go over or below a certain number, or between a given range, Excel lets you do this using so-called ‘conditional formatting’. You can apply conditional formatting to a particular cell or set of cells, meaning the formatting of the cells changes (depending on the value in the cell). For instance, say you want to highlight potential problem areas in your balance sheet. In this case, you can use conditional formatting to provide you with visual cues. For example, if your budget for pre-paid expenses is US $5000 but you’ve actually spent $6500, you can flag this up in your sheet. To do this, click on the cell to the right of Prepaid expenses (i.e. cell D16), navigate to the Format menu, and click Conditional Formatting. Next, select formatting to based on the cell value. Now select the ‘Greater Than’ operator and enter ‘5000’ in the following field. Next, click the ‘Format’ button and select the colour and font, and the font style (i.e. italics or bold text) you’d like the data to be displayed in. Once you’re done click OK. Now if you enter any number above $5000 in cell D16, the contents will change colour and bepresented in bold text. To tack on additional conditions, click the ‘Add’ button to display another Conditional Formatting dialog box. Or, if you want to delete a condition, hit ‘Delete’ and select the condition. Task 4: Start number crunching Once you’re done formatting cells, fill in the details for current and fixes assets as well as current and long-term liabilities. As you enter figures, the totals for each section will be calculated automatically. When you’re done, the figures for ‘Total Assets’ and ‘Total Liabilities and Equity’ should be equal. Task 5: Lock your cells If you plan to share your firm’s balance sheet with others, you can lock all cells containing sensitive information to prevent number tampering or accidental deletion. A locked cell can’t be changed, and the contents of a hidden cell don’t appear in the formula bar when that cell is selected. First then, highlight the cells you wish to protect, right-click and hit ‘Format’. Navigate to the ‘Protection’ tab and check the ‘Locked’ option. Repeat this process for other sections as necessary. Now for the locked cell feature to be applied, you’ll have to protect your worksheet. Navigate to Tools/ Protection/Protect Sheet. (Note: if you have more that one worksheet in a file, choose the ‘Protect Workbook’ option). This step can be viewed as a way to ‘turn the key’ in the locks you put in place for each cell. A new ‘Protect Sheet’ window will appear. Here you have three options you can select (see M below). These include: 1. Contents - enforces any protection you applied to individual cells 2. Objects - ensures the user cannot change placement or attributes of any graphics or other objects used in your worksheet 3. Scenarios - protects any scenario definitions you have created. Now check the ‘Contents’ box along with ‘Objects’ and ‘Scenarios’. Then just enter a password to ensure that others can’t unprotect your worksheet or workbook. For maximum protection, you should use a password that is easy for you to remember, but would be difficult for others. When you click OK, what happens next depends on whether you specified a password. If you did not, the work-sheet is immediately protected. If you did use a password you will see a dialog box asking you to again enter the password. This is a double-check to make sure you did not mistype the password the first time. Now when you click your mouse on OK, the worksheet is finally properly protected. Task 6: View two sheets at once First off, make sure you have only a single workbook open. Next, choose ‘New Window’ from the ‘Window’ menu. Excel will now open a second instance of the same workbook. Now choose ‘Arrange’ from the ‘Window’ menu. Check the ‘Titled’ box and the ‘Windows of active workbook’ option below. Click OK and you will now be able to view two sheets at the same time. Task 7: Hide your sheets There may be times when you want to hide a particular worksheet so that it cannot be easily accessed. This is often done to protect information on a worksheet or keep it from prying eyes. To hide a worksheet, select the worksheet you want to hide. Choose ‘Sheet’ from the ‘Format’ menu. Excel will now display a submenu. Next, select the ‘Hide’ option. The worksheet is immediately hidden. Once hidden, there is no way to tell it is ‘missing’, as there is with hidden rows and columns. If you later want to unhide the worksheet, follow the same steps we outlined before however this time select the worksheet name and choose the ‘Unhide’ option. Be sure to grab next month’s issue of Windows Middle East and check out the third and final part of our Microsoft Excel user guide, which will cover how to create charts and pivot tables, calculate sub-totals and more.||**||Spreadsheet design rules (part 2) |~|Excel-fonts---m.jpg|~|The smaller the font size, the harder it will be to read; so contemplate 'going large'. |~|Last issue we revealed four key spreadsheet design rules. This month we reveal four more. 5. Use charts It is a well-known fact that those in management positions love charts. Why? Well, because with these they don’t have to wade through oceans of data to get the info they need. They can see at a glance whether profits have risen or fallen. Busy people are far more likely to look at a chart than they are to read rows and rows of data. 6. Column widths - be logical Should all your columns be the same size? Not necessarily. But when you start designing your spreadsheet, that will be the case; they will all be the same. When considering the size of each column, focus on the size of the data you will be entering - not the size of the heading. Columns containing dates only need to be wide enough to fit in the widest date, while columns for comments may need to be quite large. Remember, you can always adjust cell formats to wrap long text if necessary. 7. Emphasize with colour Coloured backgrounds work well with borders to distinguish headings from data, different types of sub-totals and totals, months and so on. Use caution however when choosing your colours - using a combination of a lime green background for headings with purple writing and eye-popping red backgrounds for your data is not a good plan. Generally, if you’re going to make use of any dark colours, they should only be used for headings and you should always use a pale colour for text on dark colours - white and light grey are good for this purpose. 8. Big up your points The smaller the font, the harder it is to read. While size 8 fonts might look just adorable, they’re a nightmare to read for most people (even more so for those with eye problems. This is a particularly important point to keep in mind if you aren’t the only person using or viewing a spreadsheet. Size 10 or even size 12 fonts are more user friendly. ||**||Ten essential Excel shortcuts|~|KEY2---m.jpg|~||~|1. Ctrl + 1 - Displays the ‘Format Cells’ dialog box 2. Ctrl + Left Arrow - takes you to the end of a column 3. Ctrl + Home - takes you to the top of a worksheet (i.e. cell A1) 4. Ctrl + Space Bar - selects an entire column 5. Shift + Space - selects an entire row in a sheet 6. Ctrl + A - selects an entire worksheet 7. Ctrl + Page Down - takes you to the next worksheet in a given file 8. Shift + F11 - inserts a new worksheet 9. Tab - moves been unlocked cells in a protected worksheet 10. Home - moves to the beginning of a certain row. ||**||

Add a Comment

Your display name This field is mandatory

Your e-mail address This field is mandatory (Your e-mail address won't be published)

Security code