Iowa State University Cost of Goods Worksheet

User Generated

plva515

Business Finance

Iowa State University

Description

Unformatted Attachment Preview

2 Session Two: Doing Useful Work with Excel Only those who have the patience to do simple things perfectly ever acquire the skill to do difficult things easily. Unknown authors Now that you’ve mastered the basics you are ready to do really useful work with this amazing tool. In this session you will learn to use all of Excel’s basic features properly. This will put you way ahead of anybody that hasn’t been formally trained in Excel best practice. You’ll be doing simple things, but you’ll be doing them perfectly! Even after years of daily use many users are unable to properly use Excel’s fundamental features. They often reach their goal, but get there in a very inefficient way simply because they were never taught how to do things correctly. By the end of this session you’ll be astonished with how well you are working with Excel. What originally seemed like a baffling array of little colored buttons will suddenly all begin to make sense. Session Objectives By the end of this session you will be able to: Enter text and numbers into a worksheet Create a new workbook and view two workbooks at the same time Use AutoSum to quickly calculate totals Select a range of cells and understand Smart Tags Enter data into a range and copy data across a range Select adjacent and non‐adjacent rows and columns Select non‐contiguous cell ranges and view summary information Re‐size rows and columns Use AutoSum to sum a non‐contiguous range Use AutoSum to quickly calculate averages Create your own formulas Resize a column and create functions using Formula AutoComplete Use AutoFill for text and numeric series Use AutoFill to adjust formulas Use AutoFill Options Speed up your AutoFills and create a custom fill series Use the zoom control Print out a worksheet © 2008 The Smart Method Ltd 57 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-1: Enter text and numbers into a worksheet Excel beginners tend to reach for the mouse far too often. One of the keys to productivity with Excel is to avoid using the mouse when entering data. In this lesson we’ll quickly populate a worksheet without using the mouse at all. 1 Open the Sample file: First Quarter Sales and Profit. 2 Notice the difference between values and text. tip Entering numbers as text Sometimes you need Excel to recognize a number as text. Cells can contain values or text. Values can be numbers, dates or formulas (more on formulas later). If you type an apostrophe (‘) first, Excel won’t display the apostrophe but will format the cell as text. You’ll notice that the number is then left justified to reflect this. Excel usually does a great job of recognizing when there are values in a cell and when there is text. The giveaway is that text is always (by default) left aligned in the cell and values are right aligned. Look at the numbers on this worksheet. Notice how they are all right aligned. This lets you know that Excel has correctly recognized them as values and will happily perform mathematical operations using them. When a number is formatted as text you cannot perform any mathematical calculation with it. 3 Save a value into a cell. 1. note Type the value 42000 into cell B5. Notice that the mouse cursor is still flashing in the cell. Very few Excel users will make use of the Cancel and Enter At this stage the value has not been saved into the cell. buttons on the formula If you change your mind, you can still undo the value by pressing the key at the top left of your keyboard or by bar. The Enter button is the only method that will save a value into a cell without moving the cursor away from the cell. clicking the Cancel button Bar. 2. This is very useful during macro recording – an expert skill that is outside the scope of this book but is covered in the Excel Expert Skills book in this series. 58 Decide that you want to keep this value in the cell by either pressing the , or an key on the keyboard, or by clicking the Confirm button Formula Bar. 4 First Quarter Sales and Profit on left hand side of the Formula on the Enter a column of data without using the mouse. When you enter data into a column there’s no need to use the mouse. Press the key after each entry and the active cell moves to the cell beneath. Try this now with the following January sales data: www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel important 1. Type 18,000 into cell B6 2. Press the key to move to cell B7. The many ways of entering numbers 3. Do the same to enter the relevant values into the next two cells. Negative numbers ‐123.56 (123.56) Currency prefixes Excel is quite happy for you to prefix a number with a currency symbol. The currency symbol it will accept depends upon how you defined the regional options in your operating system. 5 Enter a row of data without using the mouse. When you enter a row of data you also don’t have to use the mouse. $123.56 (works in USA) £123.56 (works in UK) 1. Click in Cell C5. 2. Type 39,000 and then press the key on your keyboard. The Tab key is on the left hand side of the keyboard above the key. Notice how pressing the key saves the value into the cell and then moves one cell to the right. See Lesson 4‐3: Format numbers using built‐in number formats for an easy method of setting $, £ and € currency prefixes whatever your locale. 3. Type 43,000 in cell D5 and press the key. Commas 4. You magically move to cell C6 as Excel assumes you want to begin entering the next row. 12,234,567.78 Fractions 6 Complete the table without using the mouse. By using the or key in the right place you should be able to complete the table now without using the mouse: You must leave a space between a number and a fraction for this to work. 6 1/4 (six, space, one, /, four) The above will appear on the worksheet as 6.25 after you enter it in this way. 0 1/4 (zero, space, one,/,four) The above will appear on the worksheet as 0.25. The leading zero is needed to prevent Excel from thinking that you are entering a date. 7 8 Change the text in cell B3 to January. 1. Double‐click cell B3. Notice that there is now a flashing cursor in the cell. 2. Type uary on the keyboard to change Jan to January. 3. Press the key. Change the text in cell B3 back to Jan using the formula bar. Click once in cell B3 and then change the text in the formula bar (see sidebar). 9 © 2008 The Smart Method Ltd Save your work as First Quarter Sales and Profit-2. 59 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-2: Create a new workbook and view two workbooks at the same time 1 tip The easiest way to create a new workbook is to simply open Excel. Excel helpfully creates a workbook, unimaginatively named Book1. If you already have a workbook open called Book1, the new workbook will be called Book2... and so on. Other way of creating a new workbook  Use the keyboard shortcut +.  Add a button to the Quick Access Toolbar. Create a new workbook by opening Excel Open Excel now and see this in action. Notice that Book1 – Microsoft Excel is displayed on the Title Bar. See more details of how this is done in: Lesson 1‐10: Customize the Quick Access Toolbar and preview the printout. note What are templates? The vast majority of users know nothing about templates and simply base every worksheet upon the Blank Workbook default template supplied by Microsoft. 2 Create another new workbook. You wouldn’t want to have to open and close Excel every time you needed a new workbook. 1. Click the Office button at the top left of the screen and click the New button on the dialog: 2. The New Workbook dialog is displayed: 3. Double‐Click the Blank Workbook template. A new blank workbook called Book2 is displayed in the workbook window. The Blank Workbook template has no information in the worksheet itself, but contains all of the Excel Option settings such as the default font size and type. Templates can also contain anything that a worksheet can contain and are used to store worksheet frameworks to give you a flying start when you find that you often create very similar worksheet layouts. If you explore the New Workbook dialog a little more you’ll see that there are hundreds of pre‐built templates provided by Microsoft. Later in this book, in Lesson 3 12: Create a template you’ll learn how to create your own template library to personalize the appearance of your workbooks. 60 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel You could be forgiven for thinking that nothing has happened but you can see that the Title Bar now says: Book2 – Microsoft Excel, showing that you are now looking at a different workbook. note Finding a workbook when many are open An alternative way to quickly find a workbook when many are open is to click ViewWindowSwitch Windows. See the What are templates sidebar for more information about templates. 3 Use the buttons on the taskbar to move between workbooks. You’ll see two buttons on the taskbar at the bottom of the screen: This presents you with a list of all open workbooks. Click on the buttons to show each workbook in the worksheet window. The only difference you will see is the Title Bar changing from Book1 to Book2 because both workbooks are empty. See the sidebar for other methods of switching windows. 4 Show both Book1 and Book2 in the worksheet window at the same time. This skill often draws a gasp of amazement from my students. Believe it or not, most Excel users don’t know how to do this. You can also use the + keyboard shortcut to cycle through all open workbooks. 1. Click ViewWindowArrange All. The Arrange Windows dialog is displayed. 2. Choose the Horizontal arrangement and click the OK button. Both workbooks are now shown within the workbook window: Notice that as you click each workbook window the title bar lights up and the Close/Minimize/Restore Down buttons appear, to show that this is the active workbook. 5 Close Book2 and Maximize Book1 to restore the display to a single workbook. If you’ve forgotten how to do this, refer back to: Lesson 1‐3: Understand the Application and Workbook windows. © 2008 The Smart Method Ltd 61 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-3: Use AutoSum to quickly calculate totals anecdote I ran an Excel course for a small company in London a couple of years ago. The boss had sent his two office staff to learn a little more about Excel. Before the course began I asked the delegates how long they had been using Excel. They told me that they’d been using it for two years to do all of their office reports. Excel’s AutoSum feature is a really useful and fast way to add the values in a range of cells together. 1 Open First Quarter Sales and Profit-2 from your sample files folder (if it isn’t already open). 2 In cell A9 Type the word Total followed by the key. The cursor moves to the right and is now in cell B9: 3 Click HomeEditing (this is the Autosum button). When I showed them AutoSum they gasped in delight. “This will save us hours” they told me. I was curious how they had been doing their reports before. Believe it or not, they had been adding up all of the figures in each column with a calculator and then manually typing the totals at the bottom of each column. In this case the boss had given them Excel as he had heard it was very good. Unfortunately he had not initially seen the need to train the staff in its use. Something interesting has happened to the worksheet: With no training it seemed quite logical to them to use it like a word processor and the boss had still been delighted that his staff were using such impressive technology. Excel has placed a marquee around the number range that AutoSum has guessed we want to work with. The pattern of dots that marks the boundary of the marquee is called the marching ants (that really is the technical term for them)! The marching ants surround all of the numbers in the column above, up to the first blank cell or text cell (in this case, up to the word Jan). First Quarter Sales and Profit-2 62 =Sum(B4:B8) is your first glimpse of an Excel Formula. Formulas always begin with an equals sign. This formula is using the SUM function to compute the Sum (or total) of the values in cells B4 to B8. www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel tip 4 Press the key or click the AutoSum button more to display the total January sales: 5 Type the word Total in cell E3 and press the key once. once Entering an AutoSum using only the keyboard You can also execute an AutoSum using the keyboard shortcut: +. The cursor moves down one row and is now in cell E4. 6 Use AutoSum to calculate sales for January.. 1. Click HomeEditingAutoSum. This time AutoSum correctly guesses that you want to sum the values to the left of cell E4: 2. 7 © 2008 The Smart Method Ltd Press the key, or click the AutoSum button once more. Save your work as First Quarter Sales and Profit-3. 63 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-4: Select a range of cells and understand Smart Tags Sometimes you don’t want to add all of the values in a column. You only want to add a selection of cells that you define yourself. This lesson shows you how to do this with a different AutoSum technique. 1 Open First Quarter Sales and Profit-3 from your sample files folder (if it isn’t already open). 2 Observe the formula behind the value in cell B9. Click on cell B9 or move to it with the arrow keys on your keyboard. Look at the formula bar at the top of the screen. Notice that the cell displays the value of a calculation and the formula bar shows the formula used to calculate the value: 3 Delete the contents of cell B9. The easiest way to delete the contents of a cell is to press the key on your keyboard but you can also right‐click the cell and then click Clear Contents from the shortcut menu. 4 Change the word Total in cell A9 to USA Sales and press the key once. You don’t have to delete the word Total before typing USA Sales. When you click in a cell and immediately begin to type, the existing cell contents are replaced. The cursor moves to cell B9. 5 Select cells B4:B5 with your mouse. When the mouse cursor hovers over a cell there are three possible cursor shapes: First Quarter Sales and Profit-3 64 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel Cursor note What it does Selecting cells with the keyboard The white cross (Select) cursor appears when you hover over the centre of the cell. You can then click and drag to select a range of cells. To select cells with the keyboard hold down the key and then use the keys to select the range needed. The black cross (AutoFill) cursor appears when you hover over the bottom right‐hand corner of a cell. We’ll be covering AutoFill later in this session. The four headed arrow (Move) cursor appears when you hover over one of the black edges of the cell (but not the bottom right corner). note Selecting a large range of cells with the -click technique Beginners often have difficulty selecting cells and end up moving them or AutoFilling them by mistake. Position the mouse at the centre of cell B4 so that you see the White Cross (select) cursor. When you see the white cross, hold down the left mouse button and drag down to cell B5. If you need to select a very large range of cells it is sometimes useful to use this technique: 1. Click the cell in the top left corner of the required range. 2. Use the scroll bars to move to the bottom right corner of the required range. 3. Hold down the key. 4. Click in the bottom right corner of the required range. You have now selected cells B4 and B5 (in Excel speak we say that you have selected the range B4:B5). 6 Click the Autosum button. USA sales are shown in cell B9. Notice the small green triangle at the top left of B9. This is Excel’s way of saying: “I think you may have made a mistake”. Select cell B9 and you will see an Exclamation Mark icon. This is called a Smart Tag. 7 Hover over the Smart Tag. A tip box pops up telling you what Excel thinks you may have done wrong (see below). Of course, in this case, everything is fine. The Smart Tag thinks that perhaps we didn’t want just the USA sales – but the Smart Tag is mistaken! 8 Examine the remedial actions suggested by the Smart Tag. Click the drop‐down arrow next to the exclamation mark icon. A list of possible remedial actions is displayed. In this case you can choose Ignore Error to remove the green triangle from the corner of the cell. 9 © 2008 The Smart Method Ltd Save your work as First Quarter Sales and Profit-4. 65 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-5: Enter data into a range and copy data across a range Now that you have mastered the technique of selecting cells, you can use it to speed up data entry. When you select a range of cells prior to entering data, Excel knows that all data entered belongs in that range. Several key combinations are then available to greatly speed up data entry. 1 2 Open a new workbook and save it as Data Range Test. 3 Type London. Select cells B2:D4. The text appears in Cell B2, the top left cell in the range selected. 4 Press the Key. The cursor moves to cell B3 as it normally would. 5 Type Paris followed by the key. The cursor moves to cell B4 as it normally would. 6 Type New York followed by the key. This time something new happens. The cursor doesn’t move to cell B5 as you might expect but jumps to cell C2. 7 Type 150,000 followed by the key. The value appears in C2 and Excel moves down the column again to cell C3. 66 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 8 Press the key without entering a value to leave C3 blank. Excel moves down the column to cell C4. 9 Type 225,000 followed by the key. The cursor jumps to cell D2. 10 11 Press + twice to change your mind about leaving Paris blank. 1. Press + to move backwards to New York sales. 2. Press + a second time and you are back to the Paris cell. Type 180,000 followed by the key. moves you across the range, to cell D3. You can now appreciate how to use this technique of , , + and + to save a lot of time when entering a whole table of data. 12 13 Select cells D2:D4. Type 50% but don’t press the or keys. The challenge this time is to place the same value into cells D3 and D4 without having to type the value two more times. 14 Press +. The value is replicated into all of the other cells in the range. © 2008 The Smart Method Ltd 67 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-6: Select adjacent and non-adjacent rows and columns 1 Open First Quarter Sales and Profit-4 from your sample files folder (if it isn’t already open). 2 Select all of column A Hover over the letter A at the top of the column. The button lights up and the mouse cursor changes to a black down arrow: Click to select the entire column. The column becomes slightly shaded and a black line surrounds all of the cells. 3 Click HomeFontBold to bold face the column. Because the whole column was selected, all of the values become bold faced. 4 Click HomeFontBold once more to change the type back to normal. 5 Select all of row 4. To select a row, hover over the number on the left hand side of the row. The button lights up and the mouse cursor changes to a black arrow pointing across the row: Click to select the row. 6 Select columns B and C. Hover over the letter at the top of column B until you see the black down arrow. When you see the arrow, click and drag to the right to select both columns. First Quarter Sales and Profit-4 68 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 7 Select rows 6 and 7. Hover over the number at the left of row 6 until you see the black arrow pointing across the row. When you see the arrow, click and drag down to row 7 to select both rows. 8 Select columns A, B, C, D and E. Sometimes you will need to select a large number of adjacent columns or rows. You could drag across them but it is often easier to use the following technique: 1. Select Column A. 2. Hold down the key. 3. Select Column E. Columns A to E are selected. 9 Select rows 4 and 6. Perhaps you need to perform an operation on two non‐adjacent rows. To select rows 4 and 6 you need to: © 2008 The Smart Method Ltd 1. Select row 4. 2. Hold down the key on the keyboard. 3. Select Row 6. 69 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-7: Select noncontiguous cell ranges and view summary information Contiguous is a very impressive word! It simply means a range of cells that is split across two blocks of cells in different parts of the worksheet. Non‐contiguous ranges can be selected using both the mouse and keyboard. The keyboard method may seem a little involved at first but you’ll find it much faster once you have the hang of it. 1 Open First Quarter Sales and Profit-4 from your sample files folder (if it isn’t already open). 2 Select the contiguous range B4:D8 with the keyboard. When you need to select a contiguous range with the keyboard here’s how it’s done: 1. Use the arrow keys on the keyboard to navigate to cell B4 2. Hold down the key on the keyboard 3. Still holding the key down, use the arrow keys on the keyboard to navigate to cell D8 The contiguous range B4:D8 is selected. 3 Select the non-contiguous range B4:B8,D4:D8 using the mouse. 1. Select the range B4:B8. 2. Hold down the key and select the range D4:D8. The non‐contiguous range B4:B8,D4:D8 is selected: First Quarter Sales and Profit-4 70 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 4 Select the same non-contiguous range with the keyboard. This is a little more involved than using the simple + method used earlier. Here’s how it’s done: 1. Use the arrow keys on the keyboard to navigate to cell B4 2. Press the key (it is on the very top row of your keyboard) 3. Use the arrow keys to navigate to cell B8 4. Press + 5. Use the arrow keys to navigate to cell D4 6. Press 7. Use the arrow keys to navigate to cell D8 8. Press + one last time The non‐contiguous range B4:B8,D4:D8 is selected: 5 Obtain a total sale figures for January and March using the status bar. The status bar contains summary information for the currently selected range. Look at the bottom right of your screen. You can see the average sales and total sales (sum of sales) for January and March: 6 View the Maximum and Minimum sales for January and March using the status bar. Right‐click the status bar and click Maximum and Minimum on the pop‐up menu (see sidebar). The status bar now also displays Maximum and Minimum values. 7 © 2008 The Smart Method Ltd Close the workbook without saving. 71 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-8: AutoSelect a range of cells When data is arranged in a block (as it is in the Sales and Profit Report) it is referred to as a range (in previous versions of Excel it used to be called a list). You will often want to select a row or column of cells within a range, or even the entire range. You can select ranges by using any of the techniques covered so far but this could be very time consuming if the range encompassed thousands of rows and columns. In this lesson you’ll learn how to select range rows, range columns and entire ranges with a few clicks of the mouse. 1 Open Sales Report from your sample files folder. This report contains a range of cells. The range is the block of cells from A3 to E19. 2 Select all cells within the range to the right of cell A7. 1. Click in cell A7 to make it the active cell. 2. Hover over the right hand border of cell A7 until you see the four headed arrow cursor shape. 3. When you see this cursor shape hold down the key and double‐click. All cells to the right of A7, but within the range, are selected. Sales Report 72 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 3 note Select all cells within the range except the header row. 1. Click in cell A4 to make it the active cell. 2. Hover over the right hand border of cell A4 until you see the four headed arrow cursor shape. 3. When you see this cursor shape hold down the key and double‐click. Other ways to use AutoSelect Using the keyboard. Here’s how you would select the entire range in the Weekly Sales Report (including the header row) using the keyboard method. All cells to the right of cell A4, but within the range, are selected. Make cell A3 the active cell by navigating to it with the keys. 1. 4. Hover over the bottom border of the selected range until you see the four headed arrow cursor shape. 5. When you see this cursor shape hold down the key and double‐click. Press: ++ Cells A3:A19 are selected. 2. Release all keys and press: The entire range (except the header row) is selected. ++ The entire range (including the header row) is selected. From the Ribbon. The Ribbon method isn’t as powerful as the other methods but does provide a way to select the current range (described as the region in the dialog). Make sure that the active cell is within the range. 1. Click HomeEditing Find & Select GoTo Special... The GoTo Special dialog is displayed. 2. Click the Current Region option button and then click the OK button. The entire range (including the header row) is selected. © 2008 The Smart Method Ltd You can also use this technique to select cells to the left of the active cell or above the active cell. 4 Close the workbook without saving. 73 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-9: Re-size rows and columns 1 Open First Quarter Sales and Profit-4 from your sample files folder (if it isn’t already open). Notice that columns B, C, D and E are far too wide for their contents. It would be useful to make them narrower to keep the worksheet compact. 2 Re-size column B so that it is just wide enough to contain the January sales figures. Hover over the line separating the letters A and B until you see the re‐size cursor shape: When you see this shape, keep the mouse still and click and drag to the left. Column B will re‐size as you drag. Make it narrower so that the values just fit in the column. Notice that the column width in points and pixels are displayed as you drag (one point = 1/72 inch). note Why are you calling the pound sign a hash? You like potato and I like potahto, You like tomato and I like tomahto; Potato, potahto, tomato, tomahto! Letʹs call the whole thing off! 3 Song lyric by George Gershwin, American Composer (1898‐1937) Notice that when the column isn’t wide enough to contain the contents, hash signs are shown instead of values (if you’re used to hashes being called pound signs or number signs see the sidebar). In the USA and Canada the hash symbol is called the pound sign or the number sign. In different USA/Canada regions the single symbol has different names because it can be used to denote a number (as in contestant #5) or as a weight (as in 3# of butter). Throughout this book I will refer to the # as a hash because that is the term used in most other English speaking countries. First Quarter Sales and Profit-4 74 Re-size column B so that it is too narrow to contain the January sales figures. 4 Automatically re-size column B so that it is a perfect fit for the widest cell in the column. Hover over the line separating the letters A and B until you see the re‐size cursor shape: When you see this shape, double‐click to automatically re‐size column B. www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel note 5 Other ways to re-size rows and columns Automatically re-size every column in the worksheet in one operation. 1. Select every cell in the workbook by clicking the select all button in the top left corner of the worksheet (you can also select all by pressing +): 2. Hover over the intersection of any two columns until you see You can also re‐size rows and columns using the Ribbon. Click HomeCellsFormat. A drop‐down menu appears. the re‐size cursor shape and then double‐click. Every column is now perfectly sized. Notice that Auto‐resize has done its job rather too well. Column A is now wide enough to accommodate all of the text in cell A1. You can use the Row Height and Column Width settings to set the row or column to a specific number of points (a point is 1/72 of an inch). 6 Automatically re-size column A so that it is only wide enough to contain the longest city name (Los Angeles). You can also use the AutoFit Row Height and AutoFit Column Width options to automatically size the row or column (you achieved this more efficiently with a double‐click in the lesson). Default Width... often confuses as it doesn’t appear to work. This is because it re‐sets all columns to default width except those that have already been re‐ sized. note 2. Click HomeCellsFormatAutoFit Column Width. Notice that the text has spilled over from cell A1 into the adjoining columns B, C, D and E. This always happens when a cell contains text and the adjoining cells are empty. 7 Manually size row 3 so that it is about twice as tall as the other rows. Do this in exactly the same way you re‐sized the column but, this time, hover between the intersection of rows 3 and 4 until you see the re‐size cursor shape, and then drag downwards. Sometimes you will want to make several columns exactly the same width. © 2008 The Smart Method Ltd Select cells A4:A9. This time the column is automatically sized so that it is wide enough to contain all of the text in the selected cells. Making several columns or rows the same size. To do this, select the multiple columns and then click and drag the intersection of any of the selected columns. This will make each of the columns exactly the same width. 1. 8 Auto-resize row 3 so that it is the same size as the other rows again. 75 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-10: Use AutoSum to sum a non-contiguous range In the previous lesson, you learned how to view the sum of January and March sales using the status bar. But how can you put that value onto the worksheet? Now that you have the hang of selecting non‐contiguous ranges you can use this in conjunction with your AutoSum skills to create a formula that will calculate the total of a non‐contiguous range. 1 Open First Quarter Sales and Profit-4 from your sample files folder (if it isn’t already open). 2 Enter the text Jan/Mar Sales in cell A10 and press the key. The active cell moves to cell B10. 3 4 Re-size column A so that it is wide enough to contain the text. 1. Hover over the line separating the letters A and B until you see the re‐size cursor shape: 2. When you see this shape, keep the mouse still and click and drag to the right. Column A will re‐size as you drag. Make it wider so that the words Jan/Mar Sales comfortably fit in the column: Click HomeEditing (the AutoSum button). An AutoSum appears in cell B10 but it isn’t anything like what we want yet. It guesses that we simply want to repeat the value in the USA Sales cell. First Quarter Sales and Profit-4 76 5 Select the range B4:B8 with the mouse. www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 6 Hold down the key and select the range D4:D8 with the mouse. Notice that the non‐contiguous range B4:B8,D4:D8 is shown in the AutoSum’s formula: © 2008 The Smart Method Ltd 7 Press the key or click the AutoSum button again to view the sales for January and March in cell B10. 8 Save your work as First Quarter Sales and Profit-5. 77 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-11: Use AutoSum to quickly calculate averages Excel’s AutoSum feature isn’t only restricted to addition. It is also able to compute averages and maximum/minimum values. In this lesson we’ll use AutoSum to calculate the average sales for each month. 1 Open First Quarter Sales and Profit 5 from your sample files folder (If it isn’t already open). 2 Delete cells E3:E4. Select cells E3 and E4 and press the key on your keyboard. 3 Type the word Average in cell E3 and press the key. The cursor moves to cell E4: 4 Use AutoSum to create a formula that will show the Average New York sales. 1. Click HomeEditingAutoSum Drop down arrow (see sidebar). A drop down menu is displayed showing all of the different ways in which AutoSum can operate upon a range of cells: First Quarter Sales and Profit-5 78 2. Click Average. 3. Excel generates an Average function and inserts the cell range B4:D4. This is exactly what we want: 4. Press the key or click the AutoSum button to see the average sales for New York: again 5 Type the word Maximum in Cell F3 and then press the key. 6 Use AutoSum to create a formula in cell F4 that will show the Maximum New York Sales for this period. www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 1. Place an AutoSum in cell F4 but this time, choose Max from the drop‐down menu. This time we have a small problem. AutoSum is including the average value (23,333) in the calculation. 2. Select cells B4:D4 with the mouse The marquee corrects and the average value in cell E4 is no longer included. Notice that the Max function is now working with the range B4:D4. 3. tip Another way to bring back the blue box showing a range is to click the range in the Formula bar. 7 Change the words USA Sales in cell A9 back to Sales and press the key. 8 Press the key on the keyboard (or double-click cell B9) to bring back the marquee (shown as a blue box). 9 Adjust the marquee using click and drag so that all offices are included in the Sales total. Notice that there is a small blue spot on each corner of the range. These are called sizing handles. 10 © 2008 The Smart Method Ltd Press the key or click the AutoSum button once more to see the maximum sales the New York office managed during the first quarter of the year: 1. Hover the mouse cursor over the bottom right (or bottom left) sizing handle until the cursor shape changes to a double headed arrow. It is really important that you see the double headed arrow and not the four headed arrow or white cross. 2. When you see the double headed arrow click and drag with the mouse down to cell B8. 3. Press the key or click the AutoSum button again. Save your work as First Quarter Sales and Profit-6. 79 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-12: Create your own formulas The AutoSum tool is very useful for quickly inserting Sum(), Average(), Count(), Max() and Min() formulas into cells. Many Excel users never get any further with their formulas than this. In this session we’ll create our own formulas without the use of AutoSum. You’ll be amazed at how easy it is. 1 Open First Quarter Sales and Profit-6 from your sample files folder (if it isn’t already open). 2 Select cells A10:B10 and press the key once. The previous contents of cells A10:B10 are removed. 3 4 Type the word Costs into cell A11 and Profit into cell A12. 5 Enter a formula into cell B12 to compute the profit made in January. Type the value 83,000 into cell B11 and press the key to move down to cell B12. 1. Type: = B9‐B11 into cell B12 2. Press the key. The profit for January is displayed: important Formulas automatically recalculate whenever any of the cells in the formula change. For example: If you were to change the Paris sales to 45,000 this would cause cell B9 to re‐ calculate to 139,000. Because B9 has changed this would, in turn, cause the profit to re‐ calculate to 56,000. 6 Enter the formula again using a better technique. The method that you have just used to enter the formula works just fine but it isn’t the best method. Sooner or later you will make a mistake. For example you could easily type B8:B11 resulting in an incorrect answer. To eliminate such errors you should always select cell references visually rather than simply type them in. You can visually select cells using either the mouse or the keyboard. First we’ll use the mouse method. First Quarter Sales and Profit-6 80 1. Click in cell B12 and press the key on the keyboard to clear the old formula. 2. Press the equals key on the keyboard. 3. Click once on the value 129,000 in cell B9. www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 4. Press the minus key on the keyboard. 5. Click once on the value 83,000 in cell B11. 6. Press the key on the keyboard. If you followed the above steps carefully you will see that you have created the same formula but with a much lower possibility of making a mistake. 7 Enter the formula again using the visual keyboard technique. The very best Excel experts hardly use the mouse at all. You waste valuable seconds every time you reach for the mouse. Here’s the expert technique of visual selection via keyboard: 8 1. Use the arrow keys to navigate to cell B12 and then press the key on the keyboard to clear the old formula. 2. Press the key on the keyboard. 3. Press the key three times to move to cell B9. 4. Press the key on the keyboard. 5. Press the key once to move to cell B11. 6. Press the key on the keyboard. Enter a formula that uses the multiplication operator. This employer is very generous and pays the staff ten percent of all profits as an incentive bonus. In cell A13 type the words 10% Bonus and then press the key on the keyboard to move to cell B13. 9 important Enter a formula that uses the multiplication operator. The multiplication operator is not an X as you might expect but an asterisk (*). The other Excel operators are shown in the sidebar. The Excel Operators Name Example + Addition 1+2 You need to press + to enter an asterisk. If you are using a full size keyboard with a numeric keypad at the right‐hand side you can also use the numeric keyboard’s key. ‐ Subtraction 7‐5 Whichever key you use you’ll still see an asterisk in the formula. * Multiplication 6*3 / Division 15/5 % Percent 25% Use either the Mouse click technique or the visual keyboard technique to enter the formula shown below into cell B13 and then press the key to see how much bonus was earned: ^ Exponentiation 4^2 10 © 2008 The Smart Method Ltd Save your work as First Quarter Sales and Profit-7. 81 Learn Excel 2007 Essential Skills with The Smart Method trivia The feature that Excel uses to help you out with function calls first made an appearance in Visual Basic 5 back in 1996 and had the wonderful name: IntelliSense. The Excel implementation is called Formula AutoComplete. In 1996 I was working in Switzerland developing a ski‐ school management application. I was extremely impressed by how easy IntelliSense made my job. I didn’t have to remember hundreds of function names any more. I was even slightly worried that just about anybody could now program (but, of course, I was wrong). Recently I was in London being driven by a Hackney cab driver (Hackney cab drivers know every shortcut and back street in London). He was worried that Satellite Navigation would now mean that anybody could become a London cabbie. Lesson 2-13: Create functions using Formula AutoComplete 1 Open First Quarter Sales and Profit-7 from your sample files folder (if it isn’t already open). 2 Type the words USA Sales into cell A15 and European Sales into cell A16. Notice that the text European Sales spills over into column B because column A isn’t wide enough to contain it. 3 Re-size column A so that it is wide enough for the words European Sales to fit within the column. You learned how to do this in: Lesson 2‐9: Re‐size rows and columns. 4 Click into cell B15 and type =S into the cell. Something amazing happens: Of course, he really had nothing to worry about either! A list drops down showing every function in the Excel function library beginning with S. This is a great new feature for Excel 2007 that Microsoft call Formula AutoComplete (if AutoComplete didn’t display as expected see the sidebar). note Enabling and disabling AutoComplete. You’ve already encountered the Sum(), Average() and Max() functions courtesy of AutoSum. You may be pleased (or dismayed) to know that there are over 300 functions in the Excel function library. The good news is that most untrained Excel users only ever get to understand Sum() and Average()! As with so many other features, Microsoft allows you to turn this very useful feature off. You’d never want to do this but you may work on a machine that has had Formula AutoComplete switched off and you need to turn it on again. When you typed =S Excel listed all functions beginning with S. 5 Notice that the list now only shows functions beginning with SU and look… there’s the SUM() function we need three down in the list. Click OfficeExcel OptionsFormulas and make sure that the Formula AutoComplete box is checked. You could simply click on it with the mouse but let’s behave like an Excel pro and do it with the keyboard. 6 First Quarter Sales and Profit-7 82 Continue typing: =SU. Press the key twice to move the cursor over the SUM function. The Sum function now has a tip telling you what the function does: www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 7 Display detailed information about the SUM() function. The tip tells you a little about the SUM() function but to get the full story press the key while SUM is still highlighted in the dropdown list: The Excel help system opens showing detailed help for the SUM() function. Read the help text if you are interested and then close the help window. Notice that Excel has now left you with only =SU in the cell. Complete the formula by typing M( Notice that a little box has appeared beneath the function call. This is the Syntax (see sidebar for more information). note The syntax box The Syntax box tells you what arguments (sometimes called parameters) the function needs. The first argument has no square brackets meaning that you can’t leave it out. The argument in square brackets is optional. For such a simple function as SUM() the syntax box is hardly needed but later we’ll discover functions that require several arguments and then the syntax box will be invaluable. 8 If you want to be a real pro you should select them with the keyboard. To do this: 9 1. Press the key repeatedly until you reach cell B4. 2. Hold down the key and press the key once to select cells B4:B5. Type a closing bracket to complete the formula and then press the key. The total USA sales are displayed in cell B15. 10 11 © 2008 The Smart Method Ltd Select the cells that you need to sum (cells B4:B5) with the mouse or keyboard. Use the same technique to create a SUM() function in cell B16 to show the total European sales (cells B6:B8). 1. Click in cell B16. 2. Type = SU. 3. Press the key twice to move the cursor over the SUM function. 4. Press the key to automatically enter the SUM function into cell B16. 5. Select the range B6:B8. 6. Type the closing bracket followed by the key. The formula should now be: =SUM(B6:B8). Save your work as First Quarter Sales and Profit 8. 83 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-14: Use AutoFill for text and numeric series 1 Open First Quarter Sales and Profit-8 from your sample files folder (if it isn’t already open). 2 Delete the text Feb and Mar from cells C3:D3. Select cells C3:D3 and then press the key on your keyboard. note 3 Notice that there is a black border around the cell and a black spot on the bottom right‐hand corner. This is the AutoFill handle. If you don’t see it, refer to the sidebar. If you don’t see the AutoFill handle somebody has switched it off It’s almost certain that AutoFill will be enabled on any computer that you work on. It is such a useful feature that you wouldn’t want to disable it. Make B3 the active cell. 4 Hover over the AutoFill handle with your mouse until the cursor shape changes to a black cross. Many of my students have great difficulty with this when they try it for the first time. If you don’t see the AutoFill handle (the black spot on the bottom right hand corner of the active cell) it’s because somebody has switched AutoFill off. Bring it back like this:  You don’t want the four‐headed arrow: the cell.  You don’t want the white cross: cell.  You want the black cross: Click OfficeExcel OptionsAdvanced In the first section (Editing Options) check the box next to Enable fill handle and cell drag and drop. 5 – that would move – that would select the – the AutoFill cursor. When the black cross cursor is visible hold down the mouse button and drag your mouse to the right to AutoFill the other months: Feb and Mar. Notice the tip that appears as you drag, previewing the month that will appear in each cell. First Quarter Sales and Profit-8 84 6 In cell A18 type Monday and AutoFill down to cell A24 to show the days of the week. 7 In cell B18 type the number 1 and in cell B19 type the number 2. 8 Select cells B18 and B19. www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel tip You don’t have to enter two numbers if you want to create a numeric range. 9 AutoFill down to B24 to create sequential numbers: 10 11 In cell C18 type 9 and in cell C19 type 18. 12 AutoFill down to B24 to create the nine times table. 13 Use AutoFill to create sequential dates. If you simply type a number into a cell and then AutoFill while holding down the key you will automatically generate sequential numbers. 14 Select cells C18 and C19. 1. Type 01‐Jan‐08 into cell D18. 2. Type 02‐Jan‐08 into cell D19. 3. Select Cells D18:D19. 4. AutoFill down to D24 to create sequential dates. Use AutoFill to quickly copy values. Sometimes you will want to duplicate the value from one cell into many others to the right of, left of, beneath, or above the active cell. When a cell containing text is the active cell and it isn’t defined as a fill series (the built‐in fill series are days of the week and months of the year), AutoFill will simply duplicate the contents of the cell: Type the text Adjusted into cell E18 and then AutoFill it down as far as cell E24. The same text is now shown in each of the cells: 15 © 2008 The Smart Method Ltd Save your work as First Quarter Sales and Profit-9. 85 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-15: Use AutoFill to adjust formulas AutoFill can save you a lot of time when extending or copying text and number ranges. But the story’s not over yet. AutoFill’s ability to copy and adjust formulas is one of the most powerful tools in Excel’s impressive armory. 1 Open First Quarter Sales and Profit 9 from your sample files folder (If it isn’t already open). 2 Consider the formula in cell B9. Click onto cell B9 and view the formula displayed in the formula bar (the formula bar is at the top right of the screen grab below). The formula is =SUM(B4:B8). AutoSum created it for us in: Lesson 2‐3: Use AutoSum to quickly calculate totals. The formula uses the SUM() function to add together the values in the range B4:B8. Think about the formula that would work in cell C9 (the total sales for February). It would be: =SUM(C4:C8). Similarly the formula that would work in cell D9 (the total sales for March) would be =SUM(D4:D8). As we move to the right all that is needed is to increment the letter for each cell reference in the formula and we’ll get the right answer every time. Now AutoFill is very clever and realizes this. When we AutoFill a cell containing a formula to the right, AutoFill increments the letters in each cell reference. Most of the time that is exactly what we want. 3 AutoFill cell B9 to the right as far as cell D9. You may see a row of hashes in Cell C9. This is because the value is too wide to fit in the cell. If this is the case, AutoFit the column using the skill learned in: Lesson 2‐9: Re‐size rows and columns. First Quarter Sales and Profit-9 86 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel The correct answers for Feb and Mar sales are shown on the worksheet. Click on the Feb total cell (C9) and look at the formula in the formula bar. You can see that AutoFill has done its work perfectly, creating a sum of the values in cells C4:C8. Our five branches have sold exactly the same amount in both January and February, but a little less in March. 4 Consider the formula in cell E4. Click onto cell E4 and view the formula displayed in the formula bar. The formula is =AVERAGE(B4:D4). AutoSum created it for us in: Lesson 2‐11: Use AutoSum to quickly calculate averages. Think about the formula that would work in cell E5 (the total sales for Los Angeles). It would be: =Average(B5:D5). Similarly the formula that would work in cell E6 (the total sales for London ) would be =Average(B6:D6). As we move downward all that is needed is to increment the number for each cell reference in the formula. © 2008 The Smart Method Ltd 5 AutoFill cell E4 down to E8 to see the Average sales for each branch. 6 AutoFill cell F4 down to F8 to view the maximum sales for each branch. 7 Save your work as First Quarter Sales and Profit-10. 87 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-16: Use AutoFill options Sometimes AutoFill begins to misbehave and actually gets in the way of efficient work by wrongly anticipating what you need. 1 Open First Quarter Sales and Profit-10 from your sample files folder (If it isn’t already open). 2 3 In cell F18 type the date 1-Jan-2009. AutoFill down as far as cell F24. The cells are populated with sequential dates: 4 5 In cell G18 type the date 31-Mar-2009. AutoFill down as far as cell G24. At some time you’ll need to add transaction dates to a worksheet and will have four or five entries with the same date. AutoFill is perfect for eliminating the need to re‐type the date for each transaction, but its insistence upon incrementing the date every time could be very frustrating. Fortunately we can change the default behavior. 6 Click the Auto Fill Options Smart Tag corner of the filled cells. at the bottom right First Quarter Sales and Profit-10 88 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 7 Click Copy Cells to tell AutoFill not to increment the date. The purpose of the Fill Formatting options will be clear later in the course when you have completed: Session Four: Making Your Worksheets Look Professional. Here’s what the other options will do: Copy Cells Fill Series Fill Days Fill Weekdays Fill Months Fill Years This is what we just did. The default for dates that include the day. The date increments by one day at a time. Because 3rd April 2009 is a Friday the weekend days are omitted and the series jumps from 3rd April to 6th April. Normally this would show the same day number for each month. In this example, there are only 30 days in two of the months so 30th is shown instead of 31st. The same calendar day is shown for each subsequent year. The first cell is copied to the other cells. The date increments by one day at a time. 8 Access fill options using right-click AutoFill. 1. Click again on cell F18 to make it the active cell. 2. AutoFill once more but, this time, hold down the right mouse button. When you release the mouse button you are presented with the AutoFill options (see sidebar). This method is preferred to the Smart Tag method because it is faster (one click instead of two)! 9 Change back to Fill Series or Fill Days. In this example Fill Series and Fill Days produce exactly the same result. 10 © 2008 The Smart Method Ltd Save your work as First Quarter Sales and Profit-11. 89 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-17: Speed up your AutoFills and create a custom fill series In this lesson we’re going to learn some AutoFill techniques that will massively speed up your efficient use of the AutoFill feature. 1 Open First Quarter Sales and Profit-11 (if it isn’t already open). 2 3 Delete the contents of cells G18:G24. 4 Double click the AutoFill handle to automatically fill cells G19:G24. Type 31-Mar-09 into cell G18. This is a real master tip! Hover over the AutoFill handle (the black spot at the bottom right hand corner). When you are sure that you have the correct black cross cursor shape, double click to automatically fill down. 5 Delete all of the dates from cells G19:G24 leaving only the date 31-Mar-09 in cell G18. 6 Hold down the key and AutoFill down as far as G24 by dragging the AutoFill handle down with the mouse. Because you held the key down AutoFill simply copies the cell instead of creating a series of values. First Quarter Sales and Profit-11 90 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel This is even faster than using the right‐click method when you want to prevent the date incrementing. 7 Click OfficeExcel OptionsPopular. 8 In the Popular category under Top options for working with Excel click the gray Edit Custom Lists... button. The Custom Lists dialog appears. 9 Click in the List entries window and add four custom list entries: North, South, East and West. 10 Click the OK button. 11 Type North in any cell and AutoFill down. As you AutoFill the custom list entries appear in the worksheet. © 2008 The Smart Method Ltd 12 Delete the North, South, East, West… cells from the worksheet. 13 Save your work as First Quarter Sales and Profit-12. 91 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-18: Use the zoom control Zooming is used to magnify or reduce the worksheet. If you have a lot of rows in a worksheet and have good eyes you might want to zoom out sometimes to see more of the worksheet on one screen. 1 Open First Quarter Sales and Profit-12 from your sample files folder (if it isn’t already open). 2 Zoom in and out of a worksheet using the mouse wheel. The fastest way to zoom a worksheet is by using the mouse. Most mice these days have a wheel in the middle of the buttons. To zoom using this wheel hold down the key on the keyboard and roll the wheel to zoom in and out. 3 Zoom in and out of a worksheet using the zoom control. The zoom control is at the bottom right of your screen, Click and drag on the zoom control slider to zoom in and out of your worksheet. You can also zoom by clicking the plus and minus buttons on either side of the Zoom control. 4 Select cells A3:D9. We’re going to use the zoom dialog to make this selection completely fill the screen. 5 Click the left hand side of the zoom bar. The zoom dialog is displayed. First Quarter Sales and Profit-12 92 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 6 Select the Fit Selection option button and click the OK button. The worksheet is zoomed so that the selected cells completely fill the screen. 7 Zoom back to 100% using the Ribbon. You’ll probably find the zoom bar to be the quickest and most convenient way to zoom, but the View Ribbon also has a Zoom group containing three buttons: Use the 100% button to restore the screen to normal. © 2008 The Smart Method Ltd 93 Learn Excel 2007 Essential Skills with The Smart Method Lesson 2-19: Print out a worksheet We aren’t going to explore every option for preparing and printing a worksheet in this lesson. Printing is such a huge subject that we devote a whole session to it in: Session Seven: Printing Your Work. This lesson only aims to teach you the bare minimum skills you need to put your work onto paper. 1 Open First Quarter Sales and Profit-12 from your sample files folder (if it isn’t already open). 2 Click the Office button at the top left of the screen 3 Hover the mouse cursor over the Print button at the left of the dialog. 4 Click Print Preview from the menu at the right hand side of the dialog. 5 A preview of the printout is shown on screen: . First Quarter Sales and Profit-12 94 www.LearnMicrosoftExcel.com Session Two: Doing Useful Work with Excel 6 Click on the page to see the zoom feature working. Each time you click on the page the page zooms in and out. 7 Click the Print button screen. at the top left of the Print Preview If you don’t see this icon at the top of the Print Preview window it is because you have hidden the Ribbon. Unhide it by double‐ clicking the Print Preview Ribbon tab. The print options dialog appears: 8 Click the OK button to print your work. For this simple worksheet there’s no need to change any of the options but you can probably figure out what most of them are for. © 2008 The Smart Method Ltd 95 Session 2: Exercise 1 Open a new workbook. 2 Use AutoFill to put the three months Jan, Feb, and Mar into cells A4:A6. 3 Using only the keyboard add the following data: 4 Use AutoSum to compute London’s total profit for Jan/Feb/Mar in cell B7. 5 Use AutoSum to compute the average January profit in cell E4. 6 Use AutoFill to extend the London total in cell B7, to the Paris and New York totals in cells C7 and D7. 7 Use AutoFill to extend the January average profit in cell E4, to the February and March average profits in cells E5 and E6. 8 Select all of Column A and all of Column E (at the same time) and bold face the values in them. 9 Select row 3 and row 7 (at the same time) and bold face the values in them. 10 Select cells B4:B6 and cells D4:D6 at the same time and then read the total London and New York sales figure for Jan, Feb and March from the summary information displayed on the status bar. 11 Select cells B4:D6 and zoom the selection so that these cells fill the screen. 12 Save your work as Exercise2‐End. © 2008 The Smart Method Ltd 97 Session 2: Exercise answers These are the four questions that most students find the most difficult to remember: Q 10 Q8 Q 4 and 5 Q2 1. Hover over the centre of cell B4 so that you see the white cross cursor shape. 1. Click on the column A header. 1. Click in cell B7. 1. Type Jan into cell A4 and press the key. 2. Click and drag down to cell B6. 3. Hold down the key. 4. Hover over cell D4 until you see the white cross cursor shape. 2. Click HomeEditing Autosum (Q4). 2. Click cell A4 once to make it the active cell. 2. Hold down the key. 3. Click on the column E header. 4. Click HomeFontBold. OR Click HomeEditing AutosumAverage (Q5). 5. Click and drag down to cell D6. This was covered in: Lesson 2‐7: Select non‐ contiguous cell ranges and view summary information. 3. Hover the mouse cursor over the bottom right corner of the cell until you see the black cross cursor shape. 4. Click and drag down to cell A6. This was covered in: Lesson 2‐6: Select adjacent and non‐ adjacent rows and columns. 3. Either press the key or click the Autosum button again. This was covered in: Lesson 2‐14: Use AutoFill for text and numeric series. This was covered in: Lesson 2‐3: Use AutoSum to quickly calculate totals. If you have difficulty with the other questions, here are the lessons that cover the relevant skills: 1 Refer to: Lesson 2‐2: Create a new workbook and view two workbooks at the same time. 3 Refer to: Lesson 2‐1: Enter text and numbers into a worksheet. 6 Refer to: Lesson 2‐15: Use AutoFill to adjust formulas. 7 Refer to: Lesson 2‐15: Use AutoFill to adjust formulas. 9 Refer to: Lesson 2‐6: Select adjacent and non‐adjacent rows and columns. 11 Refer to: Lesson 2‐18: Use the zoom control. 12 Refer to: Lesson 1‐5: Save a workbook. © 2008 The Smart Method Ltd 99 Learn Excel 2007 Essential Skills with The Smart Method End piece Please provide some feedback Thank you for completing my Free Excel 2007 course. Perhaps now you will wish to teach others what you have learned yourself. At The Smart Method we use this course to teach our classroom Excel courses. Feel free to print this course out and use it in any way you wish to spread a greater understanding of Excel 2007. If the course has helped you I don’t want your money but I DO want your feedback. There’s a facility at the web site www.LearnMicrosoftExcel.com where you can pass on any comments you may have or just Email me at feedback@learnMicrosoftExcel.com. You’ll really brighten up my day if you just say “Hi, thanks for the course” or something like that. You’ll make my entire week though, if you tell me what was wrong with the course. I really want to know about typos, inaccuracies or anything at all that you found confusing or difficult. All of your feedback will be incorporated into future revisions of the course so, with your help, it will get better and better. Looking forward to hearing from you. My books If you want to improve your Excel skills further (and there’s a huge amount more to learn) I have two Excel books in print (or shortly to be in print if you are reading this prior to October 2008). They are both available from amazon.com, amazon.co.uk and any bookshop in the world. There’s a link to the right Amazon page on the website and the current status if either is not yet published. My first book includes the two sessions you have completed along with five more. It is called Learn Excel 2007 Essential Skills with The Smart Method. The second book: Learn Excel 2007 Expert Skills with The Smart Method covers all of the skills you need to be a true expert. 100 www.LearnMicrosoftExcel.com Microsoft Excel 2007 Tutorial Microsoft Excel is a useful tool in any business environment. The biggest change from the 2003 to 2007 office suite is the migration from drop down menus to what are called ribbons. This is challenging at first, but it is a much better way of organizing different functions. The Basics Opening a File Opening a CD-Rom – Insert the CD-Rom in your external drive. Click on “My Computer” and go to the disc drive to locate your CD-Rom by its name. Double click on the CD-Rom to open it and locate the files you need. Opening Excel – When you want to work independently on a spreadsheet you need to open the Excel spreadsheet. Go to “Programs” and open Excel, which you can recognize by the symbol X. Spread Sheet Layout Spreadsheets have rows that go horizontally across the page, and columns that go vertically across the page. The Columns are labeled by letters, such as A, B, C, etc. The rows are numbered 1, 2, 3, and so on. The combination of the column and row, or letter and number, describes a cell. For example, A1 describes column A row 1. Understanding this cell designation is very important. To enter formulas, we click into the appropriate cells. The formula expressed as column/row designations will then appear in the formula bar, which is labeled fx, is directly below the toolbars. When using Excel it is important to think about the format. Typically you will want to start with a Title in the first row. You will want to then label both the rows and the columns of data you will enter. If you look before there is an example of potential labels. Columns and Rows Cell Adjustment: When your Excel spreadsheet appears, the column widths are standard size. These can easily be adjusted larger or smaller in order for you to customize your sheet and accommodate your data. If you enter a large number, or one with several decimal points, and the 1 column is not wide enough to accommodate it, the sign ###### will appear. The machine continues to know what the value is even when the ##### appears and you cannot read it. By widening the column, the full number will appear. There are two ways to adjust cells: auto-adjusting and manually adjusting cells. Manual adjustment: To manually adjust the cell width, drag the cursor to either the split between letters or numbers. The arrow cursor will change to a line with an arrow on each side. When the cursor changes click and drag to the desired length. Auto-adjustment: To auto-adjust the cells place the cursor over the break between either the number or the letters and double click. Deleting a Column or Row You may from time to time want to completely delete a row or a column. Highlight the column (or row) by clicking on its letter (or number). Then go to the Edit command on your toolbar and click Delete. You can delete part of a row or column, but be careful because you can upset the symmetry of the rest of your spreadsheet. If you highlight a section of your spreadsheet and then click Edit/Delete, the machine will ask you if you want to shift your cells, or eliminate an entire row. If you make a mistake, immediately go to Edit and click Undo Delete. If you simply want to remove data from some cells and not actually remove the cell from the spreadsheet, go to Edit and click Clear. Inserting a Column or Row Highlight where you want to make “the insert.” If you want to insert an entire column (or row), again click on the letter (or number) where you want the new column (or row) to be inserted and ten go to Insert and click on what you want inserted. Again, adding individual cells, or groups of cells, as opposed to adding entire columns or rows, can throw off the symmetry of the rest of the data in your spreadsheet so be careful when making such changes. If you have made an insert error, immediately go to Edit/Undo Insert or you can also later use the Edit/Delete function to remove the unwanted column, rows or cells. ** Note: if there are pound signs in a cell where there should be numbers the cell needs to be made larger. 2 By simply making the cell larger (4.71 pixels to 5.86 pixels) the number appears. Functions Microsoft Excel has many different mathematical, statistical, and other useful functions. This is what sets Excel apart from a simple word processing software package (MS Word). Functions are entered into a cell and calculated data on the worksheet. The functions will update automatically if any of the data the function uses to calculate changes. All functions must begin with the equals sign “=” in order to be effective. If the equals sign is not used it will not calculate the formula/function. Formula Writing Operation Symbol To Add + To subtract - To multiply * To divide / Note: any time you use an asterisk your computer will think you want to multiply. If you want to make a note try making it bold or italicizing Any time you use the slash for ivied your numbers your computer will think you want to divide so if you try to enter a date as 1/11 it may not be accepted unless properly formatted. Formulas Excel does simple formulas: addition, subtraction, multiplication, division just by simply using the symbols +, -, *, /. An example of this is shown below. To enter this equation simply: 1. Select the cell desired by clicking on it with your mouse. 2. Enter “=” 3. Enter 5 + 5 4. Finally press “enter” After pressing enter the “=5+5” will disappear and 10 will appear. To edit this double click on the cell and you will see what is shown to the left You may also enter formulas in the area boxed in above. To do this simple click on the cell and then click on the white space of that text box. 3 Addition, subtraction, multiplication, and division are not that useful like shown above; it would much easier to use a calculator. However, excel does many things a calculator cannot do. Below is an example of the “sum” function. The sum function use is to take a list of data and add them together. The steps to enter this function are as followed: 1. Enter “=” followed by sum 2. Then click and drag with the mouse on the desired cells Microsoft automatically inserts the first parenthesis in the equation “=sum(A1:A6.” The second parenthesis is not required; however, it is good practice to always enter in the ending parenthesis because it gets you in the habit. After hitting enter the “=sum(A1:A6)” will disappear and the answer will be in the box. Notice the notation for entering the data the letter of the cell is first followed by the number “A1.” Then a colon “:” separates the last cell in the list. This can be typed in manually if desired instead of clicking and dragging with the mouse. Once the basic format of entering in functions is learned other functions are just as easy to create. Average: the average function “=average” sums data and divides by the number of individual pieces of data (mean). 4 Error Messages: If a word or even letter is entered in a cell where you want a formula performed, the term “#VALUE!” will appear, indication that the machine cannot perform the function requested because it does not know how to real that cell. Formatting the Numbers To enter data in a cell, click on the cell, type in the number and press Enter. When a number is entered on the spreadsheet it is automatically formatted as General. You can designate specific numbers by entering your desired format for $00.00 or %, for example, but it is recommended that for formatting you use the Format function, found at the top of the page. Go to Format and click on the first line, Cells. You then can select the format that you want. Currency, for example, will enable you to select if you want to show cents, or not (number of decimal places), if you want losses to be highlighted in red, etc. Using the format function, dates, for example can be expressed many ways, to your best liking. If you want to select a particular format for more than a single cell, you can do so by clicking your cursor on the first cell you want to format, holding down the left mouse button and then dragging it to cover the additional cells in the rows and/or columns that you would like to be similarly formatted. The entire area you want formatted will be highlighted. Go to Format – Cells and select the format you want, and the entire highlighted area will be formatted as you have directed. Percentages: Percentage calculations are frequently made when doing retailing math and it is imprtant that you understand how to enter % figures on your spreadsheet. When doing any % calculation, you must understand that a percent needs to be expressed as a decimal, i.e., 4% is expressed as .04 in order to do the math. When entering percentages onto an unformatted spreadsheet, numbers similarly need to be entering percentages onto an unformated spreadsheet, numbers similarly need to be expressed as decimals. If, however, a cellhas been previously formatted as %, when you enter the number you enter it as a whole number and the machine will know how to handle it as a percent. For example, if previously formatted, by entering 4, the machine will automatically convert it to 4%. If the cell was not formatted and you enter 4, and later format it as a %, the machine will conver it to 400%. Short-cut: left click and drag over the data you wish to re-format. Next right-click on the area you selected and select format cells. 5 The first tab that appears in the menu is number; on the right-hand side there is a list of different formatting options. When you select an option different than general there are other options on formatting the cell. Rounding: You may choose a certain format, limiting the number of decimal points or cents from a number. Even if the number shown in the cell is rounded off, however, the machine continues to know what that unrounded number will be used and the final answer may be slightly different than you would have expected, based on your (or your textbook) doing the arithmetic with a fully rounded number. Ribbon: select the data you wish to format (as in above) and click on the home tab on the ribbon. In the cells box is a drop down menu labeled format select this and drag the mouse down to the bottom and select format cells. 6 Merging cells: To merge cells there are three options: the first option is to use the shortcut, the second is to use the ribbon, and the third is to use the formatting cells menu. Short-cut: select the cells you wish to merge together by clicking and dragging. Next right click in that selected area and in the top menu is a picture in the lower left corner click on this picture to merge the cells. Ribbon: select the cells you wish to merge by clicking and dragging. Then on the home tab in the ribbon select the merge icon. Formatting Menu: first select the cells you wish to merge. Then bring up the formatting menu as shown above in cell formatting. Select the alignment tab on the top, and then check the merge box. 7 Copying Formulas Under Edit, the Copy and Paste features can be very useful. You can copy a formula in one section of your spreadsheet and transfer or paste it somewhere else on the sheet. Click on the formula you want to copy. A black box will appear around it. Go to Edit and select Copy. Then go to the cell you wish to paste the formula into and click on it. Go back up to Edit and select paste. A shorter way to do this is to right click on the cell you wish to copy, scroll down to copy and click. Then right click on the cell you wish to paste it into and select paste. Worksheet Formatting: Renaming worksheets: at the bottom of the worksheet there are different tabs (default named as sheet 1, sheet 2, sheet 3). To rename these tabs place your cursor over the tab and right-click. A menu will appear, select rename. The menu will disappear and the tab will be selected and ready to rename. Another alternative to renaming a worksheet is to simply double click on the tab. After double clicking step 2 above will show up and you will be able to rename your worksheet. Moving worksheets: To move a worksheet simply click and drag the tab. As you move the tab an arrow will appear in the spot it will be placed. Copying Worksheets Once you have developed a worksheet you may want to reuse it time and again. Having a fully formatted sheet which contains all the necessary formulas may save time as you edit and change parts of the spreadsheet. You can make a copy of the entire page and then reenter values that will be factored into your already existent formulas. Click on the entire area that you wish to copy. A black box will appear around it. Go to Edit and click Copy. Then go to the cell where you want to paste the worksheet and click paste. You can copy 8 to a space on your current spreadsheet, move to another sheet, or open an entirely new Excel spreadsheet. Color Coding Tabs/worksheets: right click on the tab to select it. Next Left click on that same tab. Select the tab color. Freeze panes: Freezing panes is useful if you have a lot of data and wish to see certain data no matter how far down/over you scroll. Under the view tab on the ribbon there is a freeze panes menu. There are three options to freeze panes: 1. You can freeze the top row. 9 2. You can freeze the first column 3. You can customize which cells you freeze by clicking on the cell just under and/or just to the right of the rows columns you wish to freeze. ***Notice the pictures to the left of the descriptions show where the panes will freeze Saving Be sure to save your work frequently! Printing Printing column/row numbers and letters: Excel does not print the cell letters and numbers as shown on excel by default. To do this you simple click on the page layout ribbon and click on the box with print next to it under headings. Printing Gridlines. In Excel 2007, gridlines don't appear on a printed worksheet or workbook by default. This article explains how you can make the gridlines appear in your printed worksheets. 1. Select the worksheet or worksheets that you want to print. To select Do this A single sheet Click the sheet tab. If you don't see the tab that you want, click the tab scrolling buttons to display the tab, and then click the tab. Two or more adjacent sheets Click the tab for the first sheet. Then hold down SHIFT while you click the tab for the last sheet that you want to select. Two or more nonadjacent sheets Click the tab for the first sheet. Then hold down CTRL while you click the tabs of the other sheets that you want to select. All sheets in a workbook Right-click a sheet tab, and then click Select All Sheets on the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.). 10 Tip When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu. 2. On the Page Layout tab, in the Sheet Options group, select the Print check box under Gridlines. NOTE If the check boxes in the Sheet Options group appear dimmed, it may be because you have a chart, image, or other object selected on the worksheet. If you cancel that selection, you'll be able to use the check boxes. 3. Click Microsoft Office Button , and then click Print. Keyboard shortcut You can also press CTRL+P. 4. In the Print dialog box, click OK. After you select the Print check box, you may want to take the following steps: Preview the gridlines. To see how the gridlines will print, press CTRL+F2 to open the Print Preview window. Gridlines are designed to print only around actual data in a worksheet. If you want to print gridlines around empty cells as well, you must set the print area (print area: One or more ranges of cells that you designate to print when you don't want to print the entire worksheet. If a worksheet includes a print area, only the print area is printed.) to include those cells. Alternatively, you can apply borders around the cells instead. To learn more about these options, see Define or clear a print area on a worksheet or Apply or remove cell borders on a worksheet. Troubleshoot printing issues with gridlines. If gridlines don't show up when you print your worksheet, or if you can't see them in the Print Preview window, make sure that the Draft quality check box is not selected. The Draft quality check box appears on Sheet tab in the Page Setup dialog box. To quickly access the Page Setup dialog box, press ALT+P, S, P. 11 If gridlines still don't print successfully, there may be an issue with your printer driver. In this case, you can try downloading the latest driver from the printer manufacturer's Web site. As a last resort, you can apply borders around the cells that you want to print. For more information, see Apply or remove cell borders on a worksheet. Printing Formulas. In Microsoft Excel 2007, it is easier to show and print formulas than in previous versions of Microsoft Excel. Use the following instructions to show and print formulas: 1. Click on the Formulas tab: 12 2. Click on Show Formulas Note: Once you have clicked on Show Formulas, any formula that has been typed in the Excel file will appear. You will notice that the formatting of the cells has changed (i.e. the cell width is quite a bit larger). In order to print the formulas on as few of pages as possible, you can shorten the cell width. Just be sure that you can still see the entire formula. Also, you may want to consider printing the formula page using Landscape orientation instead of the default Portrait. Once you have adjusted the cell widths, Print Preview the page prior to printing. This is a good habit to get into. By doing a Print Preview, you can see how many pages will be printed. So, for example if you think you can fit the formulas on one page, and the print preview is showing two pages, then you can adjust the formatting to get the formulas to fit on one page. When you are done viewing and printing the formulas, simply click on the Show Formulas button again, and the formulas will go away (leaving only the values). You will notice that the formatting of the cells has changed again (i.e. the cell widths are smaller). You will need to adjust the cell widths to ensure that all necessary information is visible. Find Find function: to use the find function press control + f. Then simply type in whatever you are looking for. (Be away the find is case sensitive) 13 Inserting a Header/Footer Header/Footer: to insert a header and footer click on the insert tab on the ribbon and find the header/footer tab. Text will appear at the top and bottom of each page: “click to add header” “click to add footer.” Click those and type what the header/footer. If you have clicked in the header or footer boxes the ribbon above will change to a special tab called design. In the Design tab there are header/footer editing options. 14 1 Session One: Basic Skills A bad beginning makes a bad ending. Euripides, Aegeus (484 BC ‐ 406 BC). Even if you are a seasoned Excel user, I urge you to take Euripides’ advice and complete this session. You’ll fly through it if you already know most of the skills covered. In my classes I often teach professionals who have used Excel for over ten years and they always get some nugget of fantastically useful information from this session. In this session I teach you the absolute basics you need before you can start to do useful work with Excel 2007. I don’t assume that you have any previous exposure to Excel (in any version) so I have to include some very basic skills. If you’re moving to Excel 2007 from an earlier version this session will de‐mystify Microsoft’s new fluent user interface (Ribbon). Session Objectives By the end of this session you will be able to: Start Excel and check your program version Maximize, minimize, re‐size, move and close the Excel window Understand the Application and Workbook windows Open and navigate a workbook Save a workbook Pin a document and understand file organization View, add, rename, delete and navigate worksheet tabs Use the Ribbon Understand Ribbon components Customize the Quick Access Toolbar and preview the printout Use the Mini Toolbar, Key Tips and keyboard shortcuts Understand Views Use full screen view Use the help system © 2008 The Smart Method Ltd 23 Learn Excel 2007 Essential Skills with The Smart Method Lesson 1-1: Start Excel and check your program version If you are using Windows XP the procedure is almost the same as described here. You should be able to figure out the differences! 1 Click the Windows Start Button your screen. 2 Click the All Programs item at the bottom of the pop-up menu. 3 4 Scroll down the list and click on the Microsoft Office item. at the bottom left of Pin Microsoft Office Excel 2007 to the start menu. You’ll be using Excel a lot so it makes sense to pin it to the start menu. This will make it appear at the top of the list in future so you’ll be able to start Excel with just two clicks. To pin to the start menu point to Microsoft Office Excel 2007 in the list, right‐click your mouse and then choose Pin to Start Menu from the shortcut menu. 5 Left-click Microsoft Office Excel 2007 to start the application. Excel starts and is displayed on the screen: 6 Check the program version. Click the Microsoft Office button then click the Excel Options button of the dialog. at the top left of the screen and at the bottom right Click Resources from the left hand sidebar and you’ll see the version number at the bottom of the dialog: 24 www.LearnMicrosoftExcel.com Session One: Basic Skills important In this example the Excel version is: Service Packs When Excel 2007 was first released it had many bugs (as do all computer programs of any size). Microsoft is very pro‐active at fixing bugs that are found and always release these fixes free of charge as a service pack. Service packs normally only fix bugs found in the original program, but Microsoft sometimes take things a little further by including new, or at least enhanced, features with their service packs. The important part is the SP1 denoting that you have service pack one installed. If this says SP2, SP3 or an even larger number this is also fine. It simply means that Microsoft have fixed even more bugs in their product since this book was published. If you do not see any reference to SPn, you have an early, un‐patched version of Excel 2007 and you should update it (the update is free) via the Internet. 7 Check the Operating System version. Click the About Button next to about Microsoft Excel 2007. A dialog is displayed with information about your copy of Excel. I had many emails from readers of my book: Learn Access VBA 2003 with the Smart Method suggesting that some of the examples didn’t work for them. In almost every case the problem was that they were using an out‐of‐date version of the application or the operating system. After applying the relevant service packs the problems vanished. For this reason it is important that you make sure your software is up to date. note What if I’m unable to upgrade my operating system or I’m using a different operating system? Click the System Info… button. The Operating System (OS) Name and Version will then be visible at the top right of the dialog: Vista Windows XP Sometimes your corporate policy will not allow you to upgrade your operating system or application service pack. In this case you can still use this book but be aware that it is possible that some of the features described may not work as expected (or may not work at all). Once again, it doesn’t matter if you have Vista running a later service pack than SP1 (or Windows XP running a later service pack than SP2). If you’re running Vista or Windows XP, any edition will suffice (it doesn’t have to be the Ultimate or Professional edition). 8 © 2008 The Smart Method Ltd Click the close button on the top right corner of each dialog to return to the Excel screen. 25 Learn Excel 2007 Essential Skills with The Smart Method Lesson 1-2: Maximize, minimize, re-size, move and close the Excel window The great successful men of the world have used their imaginations, they think ahead and create their mental picture, and then go to work materializing that picture in all its details, filling in here, adding a little there, altering this a bit and that bit, but steadily building, steadily building. Robert Collier, American motivational author, ( 1885‐1950) Now that Excel is open you are confronted with a dazzling array of buttons, switches and other artifacts. For now let’s explore the big picture by looking at how the Excel window can be sized and moved. The details will come later. 1 Understand the Maximize, Minimize and Restore Down buttons At the top right corner of the Excel window you’ll see three buttons. The buttons that you see will depend upon how the Excel window was left last time the application closed down. Normally the Excel screen is Maximized to fill the screeen and you’ll see: But if you had reduced the size of the Excel window so that it didn’t fill the screen you’ll see this instead: Try clicking the Maximize, Minimize and Restore Down buttons but be careful to click the group of three buttons at the very top of the 26 www.LearnMicrosoftExcel.com Session One: Basic Skills window and not the similar group beneath (we’ll be visiting those in the next lesson). Maximize makes the Excel window completely fill the screen. Minimize reduces Excel to a button on the bottom task bar. Click this button again to restore the window to its previous size. Restore Down makes the Excel window smaller allowing you to re‐size the window. 2 Re-size the window After clicking the Restore down button you are able to re‐size the window. Hover over either the side of the window, or a corner of the window, with your mouse cursor. The cursor shape will change to a double‐headed arrow. When you see either cursor shape, hold down the left mouse button and move the mouse (this is called click and drag) to re‐size the window. Clicking and dragging a corner allows you to change both the height and width of the window. Clicking an edge allows you to change only one dimension. 3 Move the window. Click and drag the Title Bar (the bar at the very top of the window) to move the Excel Application window around the screen. note Other ways to close down Excel There are three expert ways of closing Excel that will save a second or two of your time. 1. Double click the Office button at the top left of your screen. 2. Press the + keys on your keyboard. We’ll be learning more about keyboard shortcuts later in this session. 3. Press the + keys on your keyboard. © 2008 The Smart Method Ltd 4 Close Excel There are two common ways to close down Excel and three lesser used methods (see sidebar). Most people will use one of the following methods: Click on the Close button window. at the top right of the Excel application OR Click on the Office button at the top left of the screen and then click on the Exit Excel button at the bottom right of the dialog. 27 Learn Excel 2007 Essential Skills with The Smart Method Lesson 1-3: Understand the Application and Workbook windows Excel can be thought of as a frame (the Application window) containing one or more Workbook windows. Most of the time you will work with one Application window containing one Workbook window. The screen grab above shows the normal way of working. The Workbook window is maximized within the Application window. If the Workbook (that’s the lower group of three buttons) Restore Down button is clicked the workbook window floats within the frame provided by the Application window. 1 28 Close Excel down and re-start it again. www.LearnMicrosoftExcel.com Session One: Basic Skills note 2 What’s the point of having a floating worksheet window? Click the Workbook’s Restore Down button (that’s the one in the lower group of three buttons). The workbook floats within the Application window: At this stage in your journey of discovery, none at all. You need to know how to maximize, minimize and restore down the workbook window because you might do it by accident and need to fix things up again. In Lesson 2‐2: Create a new workbook and view two workbooks at the same time, you’ll see how Excel is able to display more than one Workbook window within the Application window. This allows you to view two worksheets at the same time. Notice that the workbook’s Maximize, Minimize and Restore Down buttons are now on the top right corner of the floating window. 3 Click the Workbook’s Maximize button. The workbook completely fills the Application window: 4 Understand the two close buttons. You’ve probably noticed that there are two close buttons on screen. One for the Application window, and one for the Workbook window. How these buttons work is quite quirky. Application close button Closes both the application and workbook windows when only one workbook is open, or no workbooks are open. Closes only the active workbook (that’s the one that is currently on display), when more than one workbook is open. Workbook close button Closes the workbook but always leave the application window open. © 2008 The Smart Method Ltd 29 Learn Excel 2007 Essential Skills with The Smart Method Lesson 1-4: Open and navigate a workbook important Organizing your sample files folder! Excel uses the analogy of a book that has many pages. In Excel terminology we use the term: Workbook for the book and Worksheet for each of the pages. We’ll be learning more about worksheets later in this session in Lesson 1‐7: View, add, rename, delete and navigate worksheet tabs. 1 Download the sample files (if you haven’t already done so). 1. Open your web browser and type in the URL: www.learnmicrosoftexcel.com When you complete a lesson that involves a sample file that is changed, you will be instructed to save the file with a postfix. 2. Click the download link on the top left of the home page. 3. Download the sample files. By the time you’ve completed the course you’ll have sample files such as: Sales Sales‐1 Sales‐2 Sales‐3 ... etc The first file is a sample file that you downloaded, and the others (with the number postfix) are interim versions as you complete each lesson. 2 Open Excel and then click on the Office button of the screen. The sample file set includes the sample file and all interim versions. 2. If your work‐in‐progress becomes unusable (for example after a system crash) you can continue without starting at the beginning again. at the top left Select Open from the drop down menu. Navigate to the folder containing the sample files and double click The Wealth of Nations to open the sample workbook. The interim versions are provided for two reasons: 1. Open the sample workbook: The Wealth of Nations. If you prefer to use the keyboard, another way to open a workbook is to hold down the key and then press the key. 3 Go to Cell ZZ3 using the Name box. If a lesson doesn’t seem to give the results described, you can load the example to give some clues about what has gone wrong. It is a good idea to place the sample files in a different folder to your saved work. If you don’t do this you’ll be over‐ writing the sample interim files (such as Sales‐1, Sales‐2 etc) with your own finished work. Excel uses the letter of the column and the number of the row to identify cells. This is called the cell address. In the above example the cell address of the active cell is B3. The Wealth of Nations 30 www.LearnMicrosoftExcel.com Session One: Basic Skills In Excel 2007 there are a little over a million rows and a little over sixteen thousand columns. You may wonder how it is possible to name all of these columns with only 26 letters in the alphabet. note Excel 2007 is big! When Excel runs out of letters it starts using two: X,Y,Z and then AA, AB, AC etc. But even two letters is not enough. When Excel reaches column ZZ it starts using three letters: ZX, ZY, ZZ and then AAA, AAB, AAC etc. ʺSpace is big ‐ really big ‐ you just wonʹt believe how vastly, hugely mind‐bogglingly big it is. You may think itʹs a long way down the road to the chemist, but thatʹs just peanuts to space”. The currently selected cell is called the Active Cell and has a black line around it. The Active Cell’s address is always displayed in the Name Box and its contents are displayed in the Formula Bar. Douglas Adams, The Hitchhikers’ Guide to the Galaxy We can also use the Name Box to move to a specific cell. To see this in action, type ZZ3 into the Name Box and then press the key. You are teleported to cell ZZ3: A compelling reason to upgrade from Excel 2003 to the new 2007 version is the sheer size of worksheets that can now be manipulated. Excel versions 97‐2003 were limited to 65,536 rows and 256 columns. Many of my clients have needed to overcome the “65,000 row problem” in earlier Excel versions. Until now that meant moving to Access (Microsoft’s database management application). Excel 2007 worksheets can have up to 1,048,576 rows and 16,585 columns. Put into more understandable terms.  You could create a single worksheet with the name of every human being on the planet and you wouldn’t run out of cells (in fact, you wouldn’t have even used half of your worksheet). 4 5 6 Return to Cell A1 by pressing +. Go to the end of the worksheet by pressing +. Use the Scroll Bars. There are two scroll bars for Excel’s workbook window. The vertical scroll bar runs from top to bottom of the worksheet window and allows you to quickly move up and down the worksheet. The horizontal scroll bar is at the bottom right hand side of the workbook window and allows you to move to the left and right in wide worksheets. Here’s how the scroll bars work:  If you filled every cell and printed out the worksheet it would be 3.25 miles (5.24 kilometres) long and 320 yards (293 Metres) wide – that’s a lot of paper. Believe it or not, some of my clients aren’t even happy with this and are now running into the “million row problem”! © 2008 The Smart Method Ltd 31 Learn Excel 2007 Essential Skills with The Smart Method Lesson 1-5: Save a workbook note 1 Save the workbook When you are editing a workbook, the changes you make are only held in the computer’s memory. If there is a power cut or your computer crashes you will lose any work that has been done since the last save. You can also use the + keyboard shortcut to save your work. For this reason you should get into the habit of regularly saving your work. Even though we haven’t changed this worksheet, let’s save it now by clicking the Save button on the Quick Access Toolbar at the top left of the screen. 2 Save the workbook with a different name. Click the Office button and then hover over the Save As item in the drop‐down dialog. You can see that it is possible to save your file in many different formats. 3 Understand file formats. Excel Workbook (the Open XML format) Until very recently every program stored its information on the hard disk in a completely different way. These incompatible formats are called binary formats. This made it very difficult to write applications that could be used together. The Wealth of Nations 32 All of this has changed with the new file format: Office Open XML. www.LearnMicrosoftExcel.com Session One: Basic Skills Microsoft have published exactly how this format works and given it away free to the world’s developer community. This allows other programs to easily work with Excel workbook files. For example, Apple’s iPhone already supports Office Open XML Email attachments. note How users of earlier office versions can read your OpenXML files Unfortunately the future has to co‐exist with the past and there are a lot of people in the world using the older versions of Office (97, 2000, 2002 and 2003). If you save your files in the new Open XML format only people running Office 2007 will be able to read them (but see the sidebar for a potential solution to this problem). Mic...
Purchase answer to see full attachment
Explanation & Answer:
Worksheet
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer


Anonymous
Great study resource, helped me a lot.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags