Excel Fundamentals Microsoft Excel Contents Understanding Workbooks. 1 Navigating in a File. 2 Typing Text or Numbers Into A Worksheet. 3 Typing Simple Formulas In A Worksheet.
5 Inserting And Deleting Worksheets. 8 Moving or Copying A Sheet To Another Workbook. 9 Changing Worksheet Tab Colours. 11 Freezing Rows And Columns.
16 Applying General Formatting. 18 Changing Font Size. 20 Applying A Border To A Range. 21 Wrapping And Merging Text.
26 Using The SUM Function To Add. 27 Calculating An Average. 28 Finding A Minimum Value. 29 Common Error Messages.
31 Understanding Quick Analysis. George’s Information Services Microsoft Excel The Charting Process. 40 Choosing The Right Chart. 41 Using A Recommended Chart.
42 Creating A New Chart From Scratch. 43 Working With An Embedded Chart. 46 Printing An Embedded Chart. 47 Creating A Chart Sheet.
48 Changing The Chart Type. 49 Changing The Chart Layout. 50 Changing The Chart Style. 51 Printing A Chart Sheet.
52 Embedding A Chart Into A Worksheet. 55 PRACTICE EXERCISE SAMPLE. George’s Information Services Microsoft Excel St. George’s Information Services Microsoft Excel UNDERSTANDING WORKBOOKS In Microsoft Excel the data you enter, whether it sheets) that have been ruled into columns and consists of numbers, text, or formulas, is stored rows.
Before using Excel it is helpful to know what in a file known as a workbook. Workbooks are the various parts and elements that make up a just like huge electronic books with pages (or workbook are. 1 2 4 3 5 6 Atheworksheet (or page) in a workbook contains 16,384 columns that are labelled using letters of alphabet. The first column in a worksheet is labelled column A, while the last is labelled XFD Afromworksheet (or page) in a workbook contains 1,048,576 rows that are labelled using numbers 1 to 1,048,576 Where a column and row intersect we get what is known as a cell.
You enter your data into these cells. Each cell in a worksheet can hold up to 32,767 characters – although it would be unrealistic to ever push it this far. Cells are referred to by their column and row labels. For example, in the screen above the cell we are pointing to is C11 – this reference is known as the cell address and is most important as it is frequently used in commands and formulas When you start typing something, you want it to appear somewhere in the worksheet.
As a consequence when the Status Bar shows Ready mode, at least one cell in the worksheet will be highlighted – this is known as the active cell. In the screen above, the active cell is cell A1 – notice that the column label and the row label also appears coloured to indicate the active cell. You can have more than one active cell – when this occurs you have what is known as a range Amany workbook (as you would expect) is made up of pages known as worksheets. You can have as sheets in a workbook as your computer resources can accommodate.
As a default, a new blank workbook normally has 3 worksheets labelled Sheet1, Sheet2, and Sheet3. Of course these labels are pretty boring and meaningless and can be changed to something more relevant The Insert Worksheet button here will insert another worksheet into the current workbook should you need it ITTraining@sgul.uk Page 1 St. George’s Information Services Microsoft Excel NAVIGATING IN A FILE Arrow Move one cell to the right, left, up or down Keys Tab Move once cell to the right Ctrl+Home To beginning file Ctrl+End To end of typed information Home Beginning of a line End End of a line Page Down Down one screen Page Up Up one screen F5 To a specific page Appear at the right and on the bottom of the screen. You may click Scroll bars the scroll arrows, drag the scroll box or click the scroll bar to move through the document.uk Page 2 St.
George’s Information Services Microsoft Excel TYPING TEXT OR NUMBERS INTO A WORKSHEET Generally when you start a new spreadsheet you wish to enter the data active. This can be done project, the first task is to enter some headings in a number of ways but the most common is to into rows and columns. To type anything into a click in it first before typing. worksheet you need to make the cell into which Try This Yourself: Before you begin ensure that there is a blank workbook on the screen… Click in cell A3 to make this the active cell, type Garden Settings and 1 2 press 3 When you press the next cell down automatically becomes the active cell.
By the way, even though the text looks like it is in cells A3 and B3 it really only is in cell A3 – since there is nothing in B3, Excel allows the spill over to be displayed giving the 4 illusion it is in 2 cells… Type press Pool Covers and Repeat the above steps and enter the remaining text in column A as shown Click in cell B2 to make this the active cell, type UK and press 5 When you press the cell to the right becomes the active cell… Enter the remaining text in row 2 as shown For Your Reference… Handy to Know… To save enter atext: new document: You In thedon’t have above exercise to use we have or named the to make 1. Click thecell Filepointer Tab on the anddesired cell and select Save As workbook adjacent cellsGarden Department active. You can simplySalesuseandthe type the required information filed mouse it inand C:\Course Files click in the forif Excel cells 2010. you want or 2.
Locate the storage folder in the Navigation Each time you even press the start arrowExcel keysittowill mostup, move likely 2. pane Press , an arrow key or to assume you down, left, orwant right.to file your workbooks in a confirm 3. Type thename a File data entry and on and click to move [Save]the cell folder called Documents which is associated pointer to another cell with the user name you use on the computer.uk Page 3 St. George’s Information Services Microsoft Excel TYPING SIMPLE FORMULAS IN A WORKSHEET The whole idea behind Excel is to get it to even other formulas, already in the worksheet perform calculations.
In order for it to do this you using the cell addresses of these numbers rather need to type formulas in the worksheet. Usually than the actual value in them. Formulas must be these formulas reference existing numbers, or typed beginning with an equal sign (=). 2 Try This Yourself: Continue using the previous file with this exercise.
Click in cell B8 to make this the active cell Type =B3+B4+B5+B6+B7 and examine what is happening on the screen 3 Press to enter the formula and move to the next cell Notice that a calculation has now been performed. We have entered a formula in B8 that says “add the values in B3, B4, B5, B6, and B7 and show them here”… Ensure that C8 is the active cell, type =SUM(C3:C7) and press 5 This is an alternative type of formula known as a “function”. Again a calculation will appear in the cell… Click in cell B8 and notice that the formula you typed appears in the Formula Bar, while the result of the calculation 6 appears in the worksheet Repeat step 5 with cell C8 Click on the File Tab and select Save to save the additions that have been made For Your Reference… Handy to Know… To enter a formula: Operators 1. Click the cell pointer on the desired cell and + Addition type the formula commencing with = - Subtraction 2.
Press , an arrow key or to * Multiplication confirm the data entry and to move the cell / Division pointer to another cell ITTraining@sgul.uk Page 4 St. George’s Information Services Microsoft Excel FILLING A SERIES A series refers to a sequence of ordered entries reducing the amount of time taken for data entry, in adjacent cells, such as the days of the week or and ensuring that the spelling is correct. Excel months of the year. The fill technique can be provides days and months as special built-in series used to create these in a worksheet for you, that you can access.
2 Try This Yourself: Before starting this exercise Open File you MUST open the file E707 Filling_1. Click on cell A4 Move the mouse pointer to the small square (the fill handle) at the bottom right corner of the cell until the mouse pointer 3 appears as a thin, black cross Drag the mouse pointer to column F Excel will fill the range with the first six months of the year… Click on cell A5 and repeat steps 2 and 3 to create the series of months with their full names You can also fill more than one 6 row at a time… Select the range A6:A12 Repeat steps 2 and 3 to fill across to column F Examine each of the series created by the filling process For Your Reference… Handy to Know… To fill a series: As you drag the fill handle across, a tool tip 1. Click on the first cell in the series appears below the fill pointer displaying the current value in the series. This is really 2.
Drag from the fill handle across as many handy when you want to end on a particular columns as required month, day or value.uk Page 5 St. George’s Information Services Microsoft Excel INSERTING AND DELETING WORKSHEETS Once you’ve decided on a structure for your worksheets inserted. However, remember that workbook, you may find that there are some deletion of worksheets is permanent and can’t be worksheets that can be deleted. Alternatively, undone using Undo, so always save your you may find that you need additional blank workbook before making these changes.
1 Try This Yourself: Before starting this exercise Open you MUST open the file File E1324 Worksheet Techniques_1.xlsx… the workbook – it Examine currently contains one worksheet named Sheet1 2 Click on the New Sheet icon at the end of the worksheet tabs A new worksheet named Sheet2 will be inserted. You 3 can also use the keyboard shortcut. Press + to insert another new worksheet This sheet is named Sheet3 and is inserted before the 4 currently selected sheet. Now let’s delete a sheet.
Right-click on the Sheet3 worksheet tab to display the shortcut menu Select Delete to remove the worksheet As the worksheet contains no data, the sheet will be deleted immediately. If a worksheet contains data, 5 Excel will ask you to confirm your actions. Repeat steps 4 and 5 to delete Sheet2 For Your Reference… Handy to Know… To insert a new worksheet into a workbook: To insert a worksheet between existing Click on the New Sheet icon to the right of worksheets, right-click on the worksheet tab the worksheet tabs before which you want to insert a new sheet, then click on Insert to display the Insert To delete a worksheet from a workbook: dialog box. Select Worksheet and click on Right click on the worksheet tab, then select [OK].
Delete ITTraining@sgul.uk Page 6 St. George’s Information Services Microsoft Excel COPYING A WORKSHEET Just as you can copy the contents of cells and have a budget workbook that contains data for ranges within a worksheet, you can duplicate several departments, you can create a worksheet worksheets within a workbook.