Under the sheets

If you’re new to Excel, this beginner’s guide will tell you all you need to know to get started with Microsoft’s spreadsheet star..

  • E-Mail
By  Cleona Godinho Published  September 1, 2006

|~||~||~|Let’s start at the very beginning by taking a look at the main components of any Excel Spreadsheet. 1. Cells - each rectangular box in a spreadsheet is called a cell. Cells are named by the column and row in which they are located. The top left cell, A1, is highlighted by default when you open a new worksheet in Excel. To navigate from cell to cell check out our quick tips below. To move from cell to cell: • Hit the Return/Enter key to move down to the row immediately below • Hold down the Shift key, then hit the Return/Enter key to move to the row above • Hit the Tab key to move over to the column to the right • Hold down the Shift key, hit the Tab key to move back to the column to the left • Use you keyboard’s arrow keys or mouse to move up, down, left, or right. 2. Fill handle - click the bottom right corner of a cell (see pic below). You will see a cursor that looks like a cross (the fill handle). Click on this and drag it to fill cell contents into cells below or to the right. For instance, if you want to copy data (or a formula) in cell A1 into cell B1, B2 and B3,then click on fill handle on cell A1 and drag it until you reach cell B3 in your sheet. 3. Sheets - When you open an Excel workbook (i.e. the file in which you work and store your data), Excel automatically opens up three sheets. To rename any one of these sheets, simply right-click on ‘Sheet 1’ or ‘Sheet 2’ and select ‘Rename’. To add additional sheets, click ‘Insert’ and choose ‘Worksheet’. 4. Formula bar - This field lets you input or edit a formula or data such as text or a number. 5. Zoom box - To zoom in on, or out of your worksheet, use the ‘Zoom’ box. To enlarge a highlighted area to fill the viewing window, simply click on the drop-down arrow and then click ‘Selection’. 6. Autosum function - By using this function you can quickly calculate the total of a column or row of numbers. This function can also be manipulated to subtract numbers. 7. Border button- Lets you frame tables (or cells) within your worksheet to make them easier to read and work with. ||**||Creating a budget sheet |~||~||~|Now that you know the main components of Microsoft Excel, we’ll walk you through the most commonly used functions, creating a basic budget spreadsheet as our example. Task 1: Enter data into Excel • In the first row of column B (Cell B1) type January (pic A). Before you hit Enter, click on the fill handle and drag it to column M • Because Excel recognises January as part of a list (the months of the year), you can use the fill handle to automatically fill in the rest of the months. This also works for days, dates and numbers (pic B) • In the second row of column A (cell A2), type Income. Under Income, list all the types of income you receive on a monthly basis (e.g. salary, rental income, investments, etc.) • Once these income types have been entered in column A, type Total Income in the next cell down, followed by Expenses in the cell below that • List all of your expenses below this (e.g. rent, phone bill, utilities, groceries, etc.) • In the cell below type Total Expenses, and following this type in ‘Savings’ Now your budget table should look similar to the one shown in pic D. Now, we’ve got the basics of our spreadsheet done, let's do a little tidying up. If you notice that some of the data in the cells overlaps the edge of the cell, don’t worry. To fix this, select columns A to M by clicking on the ‘A’ at the top of column A and dragging it to column M. • You’ll notice that between each column heading there is a line. Rest your cursor over that line and it should change into a cursor that looks like this (pic C) • When you see this cursor, double-click it. Now all of your columns should now be wide enough to accommodate the widest entry in each column (pic D). Task 2: Using borders and tables Now we’ll format your Excel spreadsheet to make it easier to read and use. • Select the entire table in your Excel spreadsheet and click on the ‘Border’ button • You’ll now see a range of different border options. Choose the ‘All Borders’ button. Next, let’s make the month columns the same witdth. Here’s how: • Select columns B through to M • Hover your cursor over the lines between the columns until you get the resize cursor then click and hold down the left mouse button • You should see a small tip that reads “Width: 9.2” or something similar. Drag the cursor until the column is the exact size you want • When you release the left mouse button all of the selected columns will be resized to the same size. Now that you’ve made all the columns of equal size, some may not fit on one screen. To fix this, first select your entire sheet, then click on the arrow next to the ‘Zoom’ box and select ‘Selection’. You’ll now find that your entire budget spreadsheet sheet fits on one screen. Task 3: Using basic formulas and functions The whole point of a budget is to work out how much you earn, how much you spend and what you have left over. To do this, you’ll need to use Excel’s Autosum function. This is a standard Excel function that allows you to do basic addition, subtraction, multiplication and division calculations. First, make sure the active cell is the cell for January’s Total Income. On the standard Excel toolbar, there’s an Autosum function button. (This looks like a Capital E, or a Capital M turned on its side). Click that and you’ll notice that =SUM() has appeared in the active cell (pic E). The Autosum feature makes adding together your income and expenses simple. After clicking on the Autosum button all you need to do is select the cells you want to add up. Simply, click on cell B4 and drag until all of the income cells for January have been selected and then press the Enter key (pic F). Now we could repeat this process for the months from February through to December, however there is a quicker way still: • Click on the cell for January’s Total Income to make it the active cell once more • Click on the fill handle (the bottom right point of the cell) and drag this across to column M (pic G) • When you release the fill handle, the formula that the Autosum feature created for January will be copied into every month • You can see that the formula has modified itself so that for February, the formula has changed to take the values from Column C (=SUM(C4:C7)) rather than taking the values from January’s column B(pic H). This shortcut will come in handy when using the Autosum feature or using other formulas in large spreadsheets. And you’re done. Total Expenses is calculated in the same way as Total Income, so make sure January’s ‘Total Expenses’ cell is active and then use the ‘Autosum’ button. You might notice that this time Excel has automatically added the cell for January’s Total Income. This happens when Excel sees a number value in the same column because it assumes that those are the numbers you want added. In this case, we don’t want that particular cell to be added. Here’s what you should do: • Instead click and drag to select all the Expenses cells for January and then hit Enter. • Now copy the expenses formula to the other cells using the process we outlined for the Total Income cells. Using the Autosum function to subtract The final task you need to perform to create your working budget is to enter the formula that will calculate each month’s ‘Savings’. First, make sure that the cell for January's Savings is the activated. This time we’ll do something slightly different with the Autosum function. We don’t want to add cells that are next to each other this time, we actually want to create a formula that will subtract one cell from another, and to complicate matters, the cells are quite far apart. Therefore, this process involves in two steps: • Select the cell for January’s Savings • Click the ‘Autosum’ button • Click on the cell that has the formula for the Total Income for January, then hold down the Ctrl key on your keyboard and click on the cell that has the formula for ‘Total Expenses’ for January (pic I). Notice that between the two cell references in the formula there is a comma. That comma serves to add the two cells together, which isn’t want we want to do. We want a formula that will subtract our expenses from our income. To do this, click on the formula bar and change that comma to a minus sign, then hit Enter (pic J). Copy the formula through to December using the fill handle. Once you’ve completed this step, you’ll have a working spread-sheet budget. Now all you have to do is enter your figures for each item in the relevant month, and the totals -along with any savings you’ve made will be automatically calculated (pic K). Task 4: Adding colour If you want to add a splash of colour to your spreadsheet before printing, here’s how: • Highlight the specific row you wish to colour and click on the ‘Fill Colour’ button on the formatting bar (pic M). • Next, choose your desired colour from the colour palette and presto! The colour will now be added to your budget spreadsheet. Task 5: Printing your spreadsheet To print a copy of your spreadsheet: • Navigate to the File menu and click 'Print Preview’ • Next, click on the ‘Set up’ button at the top of the Print Preview window. You’ll notice that the page orientation is ‘Portrait’. In order to make this spreadsheet fit on one page, we need to change the page orientation to landscape. To do this, select ‘Landscape’ (pic N) • If your sheet still doesn’t fit, click the ‘Page Break’ button and drag the blue lines to frame your budget table (pic L) • Navigate to Print Preview once again and you'll notice that the whole sheet now fits on the page (pic O) • Hit ‘Print’ on Microsoft Excel’s File Menu and your entire budget spreadsheet should now print. You now have a good knowledge of the basics - congratulations! To view this workshop in all it's visual glory, click here||**||

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