MS Microsoft Excel skills, Cheat Sheet of MS Microsoft Excel skills

Download MS Microsoft Excel skills and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity! UFHealth INFORMATION TECHNOLOGY TRAINING Microsoft Excel for Beginners training@health.ufl.edu    1  Vocabulary  Microsoft Excel is a spreadsheet program. We use it to create reports that need calculations and charts.  1. An Excel file is called a Workbook.   - Default title is Book1  2. Ribbon broken into Tabs (Home, Insert, Page Layout…)  - Tabs broken into groups (Clipboard, Font, Alignment)  3. Name box (left) and formula bar (right)  - Name box shows address of current cell  - Formula bar shows contents of current cell  4. Columns Headings are Lettered, Rows Headings are Numbered  - Columns of a building, rows of chairs  5. Worksheet navigation buttons, Worksheet tabs  - Sheet1  6. Status bar  - Excel behaves differently depending on the current "mode"     Status Bar Modes  ‐   Ready mode. This means nothing is being entered or edited on the spreadsheet.     ‐   Enter mode. This mode is when you are doing data entry, just typing in the contents.    ‐   Edit mode. Edit the contents of the current cell. Double‐click on a cell with data in it, or click inside  the formula bar for this mode.   ‐  Point mode. Used when linking to cell addresses within a formula or from an Excel dialog window.  1 3  4  5  6  2    2  Keyboard Navigation   Key  Ready  Enter  Edit  Point  Enter  Move Down  Accept changes and move down  Shift‐Enter  Move Up  Accept changes and move up  Tab  Move Right  Accept changes and move right  Shift‐Tab  Move Left  Accept changes and move Left    Arrow Keys  Moves to another cell  Moves between  characters in cell  Points to an  address of a cell    Home  Moves to first column  Moves to the front  of the line in the cell  Points to cell in  column A  Ctrl‐Home  Moves to the beginning cell of the  worksheet (A1)    Points to the  beginning of the  worksheet    Ribbon  The images of Excel in this packet were copied from a wide screen monitor. With the wide screen the  ribbon is stretched across the window and I can see all the buttons. If you are working on a narrower  window, Excel will try to clump the groups together and the layout may look a little different than the  ones shown here, but all the buttons will be there.    Here we can see how the font group is now three buttons high, and how some of the buttons like Cut  and Copy have lost their text labels.    Clipboard  Cut, Copy and Paste are clipboard features built into Windows. The clipboard is a temporary storage  place for pictures and data. The Windows clipboard can only store one item at a time. Microsoft Office  has a Multi‐Clipboard that can store 24 items, but the Paste button and the shortcuts for the Paste  option only correspond to the most recently copied item. The clipboard pane must be displayed to be  able to use this feature.   Cut –  Copies selection to the clipboard. If the selection is text or an image, it will  disappear. If it’s a cell, Excel waits until you paste it to delete the original cell.  Copy – Copies selection to the clipboard.  Paste – Retrieves most recent text/object on the clipboard.    3  Formatting Cells  The most formatting options are found on the Home Tab. All the options can be found in the Format  Cells window. This contains several tabs to help us format the contents of our spreadsheet. This window  can be opened by using the   More Options button at the end of the Format, Alignment and Number  groups. You can also use the Keyboard Shortcut – Ctrl‐1 or choose Format Cells… from the right‐click  shortcut menu.  Font  1. Font – Sets the font of the selected cell(s). Fonts are  different ways to show the same letters.  2. Font Size – Sets the size of the letters (the font). Larger  numbers give larger fonts.   3. Increase Font – Increases the font size  4. Decrease Font – Decreases the font size  5. Bold – Makes the selected cell(s) Bold  6. Italic – Makes the selected cell(s) Italicized  7. Underline – Makes the selected cell(s) Underlined. The drop down has a double underline.  8. Borders – Adds and removes borders for the selected cell(s). The drop down has More Borders…   9. Fill Color – Changes the background color of the selected cell(s).  10. Font Color – Changes the color of the font of the selected cell(s).   11. More Options – This button will open the Format Cells dialog window.  Alignment  1. Top Align – Vertically aligns to the top of the cell.  2. Middle Align – Vertically aligns to middle of the cell.  3. Bottom Align – Vertically aligns to the bottom of  the cell.   4. Orientation – Rotates the contents of the cell to  the currently displayed option.   5. Wrap Text – Displays contents on multiple lines within the cell's column width.  6. Align Text Left – Horizontally aligns the contents to the left side of the column.  7. Center – Horizontally aligns the contents to the center of the cell.   8. Align Text Right – Horizontally aligns the contents to the right side of the cell.   9. Decrease Indent – Decreases the space between the text and the cell border   10. Increase Indent – Increases the space between the text and the cell border  11. Merge and Center – Joins selected (adjacent) cells into one cell and centers the result. If there is  data in more than one cell, Excel will only keep the information from the upper left cell.   12. More Options – This button will open the Format Cells dialog window to the Alignment Tab.     1  2  3  4        5  6  7  8  9  10  11    1  2  3  4  5                6  7  8  9  10  11  12    6  Fill Handle  The Fill Handle is in the bottom right corner of the selected cell. When  you place your mouse over this handle, it changes from a thick white  cross, to a thin black cross. Once you see the thin cross (no arrows) you  can click and drag the cell to fill its contents in a single direction (up,  down, left or right). If you want to go in two directions, you must first  complete one way, let go of the mouse and then drag the handle in the  second direction.  When you use the Fill Handle to pull down a single number or plain text, it will copy the data. When you  use the Fill Handle to pull down a text with numbers, a date, a month or a weekday it will fill in a series.    Text    123    Exam 1    2/1/02   February   Friday  Text    123    Exam 2    2/2/02   March    Saturday Text    123    Exam 3    2/3/02   April    Sunday    When you select two or more numbers (including dates) and then use the Fill Handle, Excel will fill in the  series, following the original pattern of the selected cells. It can only follow simple addition and  subtraction patterns.    123    5    100    2/01/17  124    4    110    2/08/17  125    3    120    2/15/17  126    2    130    2/22/17  Building an Equation  You can directly type in values, but that data stays constant. If you want to have the answers to your  equations update as you change your data, you should use the cell addresses. You will see the cell  addresses change colors so you can tell which ones are used in your equation.  Type in the exact cell address  Cells are labeled by their row and column headings. Rows are numbered and go horizontally across  (rows of chairs) and columns are lettered and go vertically top to bottom (columns of a building). When  we refer to the address of a cell, we use the column letter then the row number such as A1.  - Click in the cell where the answer will appear  - Press the Equal sign (=)  - Type in the cell address you want to use in your equation  - Accept the answer or press the next math operator (+, ‐, *, /, ^)      A  B  C  1  1  2  =a1+b1  2          7  Use the mouse to point to the cell address  The mouse and arrow keys are both "pointers". If you press the equal sign and then use the mouse to  click on another cell, Excel will put you into a "POINT" mode, and place the address of the cell you  clicked on in your equation.  - Click in the cell where the answer will appear  - Press the Equal sign (=)  - Use the mouse to click on the cell you want to use in your equation  - Accept the answer or press the next math operator (+, ‐, *, /, ^)      A  B  C  1  1  2  =A1  2          Mathematical Operations  To let Excel know you expect it to "do math" you need start your cell with an equal sign (=).  ‐ Addition, plus sign (+)  = 5+2  result  7  ‐ Subtraction, hyphen (‐)  = 5‐2  result  3       (also used for negative)  = ‐5  result  ‐5  ‐ Multiplication, asterisk (*)  = 5*2  result  10  ‐ Division, slash (/)  = 5/2  result  2.5  ‐ Exponent/Power, caret (^)  = 5^2  result  25    AutoSum  We can build equations to do math on a large number of cells, but there are functions built into Excel  that can help us automate the most common ones: Sum, Average, Count, Maximum, Minimum. On the  far right of the Home tab you'll find the sigma ( ∑ ).       When you click on the word AutoSum, you'll get a sum  function. There is a dropdown list at the end of the  button that will show more function options.  The AutoSum button looks for numbers above or to the  left of the cell to choose the range (the set) of numbers.   Make sure to press enter or click the check to accept as  soon as the function shows up. If you click outside the  cell while you see the function, you may break the  equation.    8  Exercise 1: Customers    Resizing Columns  1) Put your mouse on the line between any two Column  letters. It will turn into a 2‐way arrow.   a. Hold down the mouse button and drag to resize   b. Double‐click between the headings to "AutoFit"    2) Select the entire worksheet by clicking on the triangle above the Row 1, left of the Column A  a. Try to resize any Column; all the selected columns will change  b. Double‐click between the headings to have it "Auto fit"    Freeze Panes (Lock Titles to Top of Page)  1) Press Ctrl‐Home on the keyboard to return to Cell A1    2) Turn to the View Tab in the Ribbon    3) Find the Option Freeze Panes   a. Choose Freeze Top Row  b. Scroll down through the worksheet to see the titles in Row 1 stay at the top        11  Exercise 3: Items by Quarter  Turn to the next worksheet at the bottom of the window, Items by Quarter.  Insert Rows  1) Select Row 1 and Row 2   Click on the row heading 1 and drag to  row heading 2    2) Right‐click Inside the selection   Choose Insert    Merged Title  1) In Cell A1 type: Quarterly Sales Report    2) Select Cells A1 through E1   Click the Merge and Center button    3) Format: Bold, Fill, Border    Fill Handle Across  1) In Cell B3, delete Qtr 1, and type: 1st Qtr    2) Drag the fill handle for Cell B3 across to Cell E3    3) Center and Bold the new titles    Total Row (AutoSum)  1) In Cell A8 type: TOTAL    2) In Cell B8 Click on the AutoSum button   =SUM(B4:B7)   Press Enter or click the check to accept (2079)    3) Drag the Fill handle in Cell B8 to Cell E8 to fill in the "sum" pattern for each quarter            The Fill Handle is the small square in the  bottom right corner of a selected cell.    12  Exercise 4: Sales Report  Turn to the next worksheet at the bottom of the window, Sales Report.  Format  1) Row 1 ‐> Bold    2) Column B ‐> Accounting ($)    3) Column C ‐> Centered Aligned    4) Cell C5 ‐> Right Aligned    Math  Total for each line item will be the Price times the Quantity.   1) Go to Cell D2    2) From the keyboard Type:    =    3) With the mouse click on Cell B2 ($10.00)    Cell D2 should now have =B2    4) From the keyboard type:    *    5) With the mouse click on Cell C2 (5)   Cell D2 should now have =B2*C2    6) Press Enter or click the check to accept   Answer: $50.00   If needed return to Cell D2    7) Drag the Fill Handle for Cell D2 to Cell D4  to fill in the pattern for the formula    Grand Total  1) Move to Cell D5    2) From the Home tab click on the AutoSum  ∑   =SUM(D2:D4)    4) Press Enter or click the check to accept    Answer: $600.00    3) Change Cell B2 to $12.50 and press  enter or click the check to accept   Grand Total should be $612.50  SRE eS George's University of London j Excel | Fundamentals Microsoft Excel St. George’s Information Services Microsoft Excel ITTraining@sgul.ac.uk Page 1 St. George’s Information Services UNDERSTANDING WORKBOOKS In Microsoft Excel the data you enter, whether it consists of numbers, text, or formulas, is stored in a file known as a workbook. Workbooks are just like huge electronic books with pages (or sheets) that have been ruled into columns and rows. Before using Excel it is helpful to know what the various parts and elements that make up a workbook are.  A worksheet (or page) in a workbook contains 16,384 columns that are labelled using letters of the alphabet. The first column in a worksheet is labelled column A, while the last is labelled XFD  A worksheet (or page) in a workbook contains 1,048,576 rows that are labelled using numbers from 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  A workbook (as you would expect) is made up of pages known as worksheets. You can have as many 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 1 2 5 3 4 6 Microsoft Excel ITTraining@sgul.ac.uk Page 2 St. George’s Information Services NAVIGATING IN A FILE Arrow Keys Move one cell to the right, left, up or down 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 Scroll bars Appear at the right and on the bottom of the screen. You may click the scroll arrows, drag the scroll box or click the scroll bar to move through the document. Microsoft Excel ITTraining@sgul.ac.uk Page 5 St. George’s Information Services FILLING A SERIES A series refers to a sequence of ordered entries in adjacent cells, such as the days of the week or months of the year. The fill technique can be used to create these in a worksheet for you, reducing the amount of time taken for data entry, and ensuring that the spelling is correct. Excel provides days and months as special built-in series that you can access. Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E707 Filling_1.xlsx...  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 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 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… To fill a series: 1. Click on the first cell in the series 2. Drag from the fill handle across as many columns as required Handy to Know…  As you drag the fill handle across, a tool tip appears below the fill pointer displaying the current value in the series. This is really handy when you want to end on a particular month, day or value. 2 3 6 Microsoft Excel ITTraining@sgul.ac.uk Page 6 St. George’s Information Services INSERTING AND DELETING WORKSHEETS Once you’ve decided on a structure for your workbook, you may find that there are some worksheets that can be deleted. Alternatively, you may find that you need additional blank worksheets inserted. However, remember that deletion of worksheets is permanent and can’t be undone using Undo, so always save your workbook before making these changes. Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E1324 Worksheet Techniques_1.xlsx…  Examine the workbook – it currently contains one worksheet named Sheet1  Click on the New Sheet icon at the end of the worksheet tabs A new worksheet named Sheet2 will be inserted. You can also use the keyboard shortcut...  Press + to insert another new worksheet This sheet is named Sheet3 and is inserted before the 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, Excel will ask you to confirm your actions...  Repeat steps 4 and 5 to delete Sheet2 1 2 3 4 For Your Reference… To insert a new worksheet into a workbook:  Click on the New Sheet icon to the right of the worksheet tabs To delete a worksheet from a workbook:  Right click on the worksheet tab, then select Delete Handy to Know…  To insert a worksheet between existing worksheets, right-click on the worksheet tab before which you want to insert a new sheet, then click on Insert to display the Insert dialog box. Select Worksheet and click on [OK]. 5 Microsoft Excel ITTraining@sgul.ac.uk Page 7 St. George’s Information Services COPYING A WORKSHEET Just as you can copy the contents of cells and ranges within a worksheet, you can duplicate worksheets within a workbook. This technique is ideal for replicating layouts. For example, if you have a budget workbook that contains data for several departments, you can create a worksheet for the first department and then copy it to create identical worksheets for other departments. Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E1324 Worksheet Techniques_1.xlsx...  Right-click on Sheet1 to display the worksheet shortcut menu  Select Move or Copy to display the Move or Copy dialog box  Click on Create a copy so it appears ticked, then click on [OK] The new worksheet is named Sheet1 (2). Let’s create a “template” from this worksheet by deleting unwanted data...  Select the range B7:E9, then press to clear it  Repeat step 4 to clear the ranges B14:E23, G7:J9 and G14:J23, then press + to return to cell A1 Now we can copy this “template” to create additional worksheets...  Repeat steps 1 to 3 three times to create three copies of the template worksheet – this time without data The final worksheet should be named Sheet1 (5) 1 2 For Your Reference… To copy a worksheet: 1. Right-click on the worksheet to copy, then select Move or Copy 2. Click on Create a copy so it appears ticked 3. Click on [OK] Handy to Know…  You can copy the current worksheet using the HOME tab by clicking on Format in the Cells group, then clicking on Move or Copy Sheet.  The Before sheet options in the Move or Copy dialog box allow you to position the copied worksheet where you want. 3 6 Microsoft Excel ITTraining@sgul.ac.uk Page 10 St. George’s Information Services CHANGING WORKSHEET TAB COLOURS To make it easier for you to distinguish between worksheets, Excel enables you to change the colours of worksheet tabs. This allows you, for example, to quickly distinguish between different financial years, departments or months. The active sheet appears as underlined in a gradient version of the selected colour, while inactive tabs will display a solid colour background. 2 Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E1324 Worksheet Techniques_7.xlsx...  Click on the Admin worksheet tab to select the worksheet  Right-click on the worksheet tab to display the shortcut menu, then point to Tab colour This will display a palette of colour options…  Click on Red under Standard colours to apply the colour to the tab  Right-click on the Maintenance worksheet tab to display the shortcut menu, click on Tab colour, then click on Blue under Standard colours Notice how the Admin worksheet tab colour is now a solid rather than a gradient…  Repeat either technique to apply the following colours: Shop Yellow IT Green  Click on the Admin worksheet tab to view the results 3 4 5 6 For Your Reference… To change the colour of a worksheet tab: 1. Right-click on the worksheet tab to display the shortcut menu 2. Point to Tab colour to display a palette of colour options 3. Click on the desired colour Handy to Know…  To apply the same colour to two or more sheets at once, select them first. Hold down to select consecutive worksheets or hold down to select non-consecutive worksheets. Microsoft Excel ITTraining@sgul.ac.uk Page 11 St. George’s Information Services GROUPING WORKSHEETS Worksheet grouping enables you to make the same change at once to all selected worksheets. This feature is useful in situations where your worksheets have identical layouts or text. For example, if you want to format the heading for multiple worksheets, you simply group the worksheets, make a change to one worksheet and the other worksheets will reflect the change also. 1 Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E1324 Worksheet Techniques_8.xlsx...  Click on the Admin worksheet tab, hold down , then click on the Shop worksheet tab to select the first three worksheets  Click in cell A1 to select the cell  Click on the HOME tab, then click on Italics in the Font group This will italicise the text in cell A1 on this and all other worksheets in the group…  Click on the Maintenance worksheet tab, then the Shop worksheet tab to see that the changes have been applied here  Click on the IT worksheet tab to see that the changes have not been applied to this worksheet Since this was not part of the grouped sheets the changes have not been applied here. Notice too that clicking on a tab deselects the previous grouping 2 3 4 5 For Your Reference… To group worksheet tabs: 1. Click on the first worksheet tab 2. Hold down , then click on the last worksheet tab Handy to Know…  To deselect a group, either click on the tab of a worksheet that is not in the group, or right- click on a tab and select Ungroup Sheets.  Most formatting and text changes done on a worksheet in a group will be applied to other sheets in that grouping. Microsoft Excel ITTraining@sgul.ac.uk Page 12 St. George’s Information Services FREEZING ROWS AND COLUMNS When you lay out your data in rows and columns, it is most likely that your headings end up at the top or to the left of your data. If you have a large amount of data, you may find that when you scroll across or down to particular cells, the headings scroll out of view. This problem can be resolved by freezing the rows and/or columns that hold the headings. Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E1324 Worksheet Techniques_11.xlsx...  Click on the Maintenance worksheet tab, then spend a few moments examining the worksheet Depending on your screen, it is possible that you won’t be able to see all of the figures on the screen at once...  Click in cell B6 to select the cell  Click on the VIEW tab, click on Freeze Panes in the Window group, then select Freeze Panes Thin black lines appear above and to the left of the selected cell. This indicates that the areas above and to the left are frozen...  Scroll to the right until Yearly Average in column L appears next to column A  Scroll down until Overheads in row 25 is below row 5  Press + to move to cell B6 – this is our temporary home cell, as the cells above and to the left are frozen  On the VIEW tab, click on Freeze Panes in the Freeze Panes group, then click on Unfreeze Panes to unfreeze the rows and columns 3 4 For Your Reference… To freeze panes in a worksheet: 1. Click in the cell below and to the right of the area you want to freeze/unfreeze 2. Click on the VIEW tab 3. Click on Freeze Panes in the Window group, then select Freeze Panes Handy to Know…  If you want to freeze only the rows above the selected cell (leaving all columns unfrozen), select the cell in column A of that row – e.g. to freeze rows 1 to 6, click in cell A7. The same applies to freezing only columns and leaving the rows unfrozen: select the cell in row 1. 5 Microsoft Excel ITTraining@sgul.ac.uk Page 15 St. George’s Information Services SELECTING COLUMNS If you want to make changes to an entire column, such as bolding all of the headings in a column or changing the font of all the cell entries, you must first select the column. This is done by clicking on the column header directly above the column. Remember that any changes you make will apply to every cell in the column all the way down to row 1,048,576! Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E705 Ranges_1.xlsx...  Press + to make cell A1 the active cell  Move the mouse pointer to the column heading for column B Notice that the mouse pointer changes to a black arrow pointing down the column…  Click once to select the column This time the row headers change to orange to indicate that at least one cell (but not all) in each row is selected…  Click in cell D6 and press + This key combination also selects an entire column…  Click on the column header for column B to select it  Hold down and click on the column header for column D This time, columns B, C, and D are all selected…  Click in the column header for column A, then hold down the left mouse button and drag the mouse pointer across the column headings to column E For Your Reference… To select an entire column: 1. Click on the column heading of the column that you want to select OR 1. Click in any cell in the column and press + Handy to Know…  Make sure that you check your worksheet carefully after you’ve made changes to entire columns. Remember that all of the cells in that column are affected – even those in rows below the visible area. 2 3 4 6 Microsoft Excel ITTraining@sgul.ac.uk Page 16 St. George’s Information Services UNDERSTANDING FORMATTING In Excel there are always two aspects to a number: how the number presents on the screen (known as formatting) and the underlying value of the number. Take 2% as an example – on the screen it is formatted to appear as a number with a percentage sign, whereas the real value in the cell is .02. Number Formatting – The Veil Placed Over Numbers All calculations in Excel are performed using numbers – this is only logical. So, when you want to perform a calculation, you type the numbers in various cells, then create formulas to reference those numbers. How do you show what those numbers represent? For example, how do you show you are working with currency, or percentages, or even dates (which in Excel are really numbers)? Excel allows you to show these representations using number formatting. With number formatting you change the way a number looks so that it makes immediate sense to the reader of your worksheet. The underlying value of number, however, remains unchanged. For example, instead of showing sales tax in a worksheet as .1 you show it as 10%, to show 12889.95 as currency it would appears $12,889.95 or €12,889.95 (depending upon the currency you are working with), and to show 44104 as a date you show it as 30-Sep-2020 (remember, dates are actually numbers representing the number of days from January 1, 1900). The following worksheet contains formatted numbers: With the formatting removed from the numbers the worksheet looks as follows: Formatting can also be applied as you type. For example, if you type 30/9/2020 Excel will place the number 44104 in the cell but will format this number as a date and show it as you typed it. There are also a range of number formatting options on the ribbon that allow you to apply formatting to numbers after they have been entered into a worksheet. Microsoft Excel ITTraining@sgul.ac.uk Page 17 St. George’s Information Services APPLYING GENERAL FORMATTING The Number Format command in the Number group on the HOME tab contains a drop arrow that provides a gallery of the more commonly used number formats. You can apply these formats easily and quickly to a selected cell or range of cells in the worksheet. Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E1315 Number Formatting_1.xlsx…  Click in cell D4, hold down , then click in cell D13 to select the range containing dates  Click on the HOME tab, then click on the drop arrow for Number Format in the Number group to see a gallery of number formats  Click on Long Date to make the short dates in the selected range appear as long dates  Click in cell E4, hold down , then click in cell E13 to select the range containing units of measure  Click on the drop arrow for Number Format, then select Number to display these as numbers with 2 decimal places  Repeat the above steps to change G4:G13 to Currency  Repeat the above steps and change the following ranges as shown: H4:H14 Percentage I4:I4 Accounting G15:I15 Currency 2 For Your Reference… To apply general formatting to numbers: 1. Select the range to format 2. Click on the HOME tab, then click on the drop arrow for Number Format in the Number group 3. Click on the desired number format 7 Handy to Know…  Excel may appear to round values up or down as necessary – however, the value in the cell does not change. Sometimes you’ll see minor rounding discrepancies.  The Currency format shows the currency format and symbol appropriate to the country your computer is configured for. Microsoft Excel ITTraining@sgul.ac.uk Page 20 St. George’s Information Services UNDERSTANDING BORDERS Borders are lines that are placed around the edges of individual cells or ranges. The lines may be thin, thick, solid, dashed, black or coloured, or even double lines. The reason for using borders is that the lines can be used to group together data or indicate totals, or to draw the user’s attention to critical cells that may need special data entry. Here are some examples. A Worksheet without and with Borders Borders can be used to apply a structure. Here’s the same worksheet shown without borders and then with borders applied. The use of borders helps to highlight the totals and separate them from the other data. Border Variations Borders can be applied to all four sides of a cell, or to individual sides of a cell. The following examples show a cell without a border, with an outside border and a top and double bottom border. Microsoft Excel ITTraining@sgul.ac.uk Page 21 St. George’s Information Services APPLYING A BORDER TO A RANGE You can apply a border to a range of cells. This allows you to place an outline around them to indicate that the cells are somehow related to each other, or to place borders between cells to indicate that they are in separate groups. Borders can be used in ranges of cells to create a more form-like appearance. The borders available for single cells can also be applied to ranges. Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E730 Applying Borders_2.xlsx...  Select the range A5:A11  Click on the drop arrow for Borders and select Outside Borders  Click away from the range to see the border An outline has been placed around the cells...  Repeat steps 1 and 2 to apply an outline border to each of the following ranges in the order that they are listed: B5:B11, C5:C11, D5:D11, E5:E11, F5:F11, G5:G11, H5:H11, I5:I11, A5:I5, A11:I11 B13:B19, C13:C19, D13:D19, E13:E19, F13:F19, G13:G19, H13:H19, I13:I19, A13:I13, A19:I19 You can hold down and select several of these ranges at once before applying the border...  Click away from the last selected range to see the result For Your Reference… To apply a border to a range: 1. Select the range 2. Click on the drop arrow for Borders in the Font group on the Home tab 3. Click on the border option of your choice Handy to Know…  You can copy a border between cells, for example, from one table to another, using Paste Special. Select the cells, click on Copy , click on the first cell of the second range and click on the drop arrow for Paste . Select Paste Special, click on Formats and then click on [OK]. 1 3 5 Microsoft Excel ITTraining@sgul.ac.uk Page 22 St. George’s Information Services WRAPPING AND MERGING TEXT Microsoft Excel will allow long cell entries to spill across to other adjacent cells to the right as long as those cells are empty. If those cells contain data the spill-over will be chopped off. If you need to place long text entries in a cell you can arrange for Microsoft Excel to wrap the text within the cell and also merge that cell with others to accommodate the longer text entry. Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E723 Cell Alignment_9.xlsx...  Click in cell A5 This cell contains a long text entry that spills across several columns…  Click on the Expand Formula Bar tool to the right of the formula bar to see all of the text  Click on the Wrap Text command in the Alignment group on the Home tab to wrap the text in cell A5 Notice how the row height has now increased…  Hold down the key and click in cell E5 to select the range A5:E5  Click on the drop arrow for Merge & Centre in the Alignment group and select Merge Cells to merge the cells in the range  Move the mouse pointer to the bottom of the row 5 heading border and drag the row height up until you reach 30 points For Your Reference…  To wrap text - click in the cell to merge and click on the Wrap Text command in the Alignment group on the Home tab  To merge text - click on the drop arrow for Merge & Centre in the Alignment group and select Merge Cells Handy to Know…  In the example above, wrapping forced the text into one cell and Excel expanded the row height so that all of the text was accommodated. We then merged the text across several horizontal cells in the exercise above so that we could reduce the row height to a more acceptable level.  1 3 5 Microsoft Excel ITTraining@sgul.ac.uk Page 25 St. George’s Information Services PRACTICE EXERCISE Number Formatting Tasks: Completed: Before starting this exercise you MUST have completed all of the topics in the chapter Number Formatting…  Open the workbook called PE_Number Formatting.xlsx (it can be found in the same folder as the student files)   On the Cargo worksheet, apply formatting to the dates and figures so that they appear as shown in sample A on the next page This will involve applying a date format, thousands separator, setting the number of decimals and applying the currency format...   On the Purchases worksheet, apply formatting so that the figures appear as shown in sample B on the following page The currency formats should be $, € Euro (€ 123), R English (South Africa) and ETB Amharic (Ethiopia) respectively. You’ll need to widen the columns a little to make room for the characters added by the formatting...   Use the Save As command to save the workbook as PE_Number Formatting (Completed).xlsx  Microsoft Excel ITTraining@sgul.ac.uk Page 26 St. George’s Information Services UNDERSTANDING FUNCTIONS Imagine having to create a formula that calculated the monthly payments on a loan, or the average of over 100 cells – these would require complex or long formulas that would be time consuming to develop. This is the role of hundreds of arithmetic functions that have been pre-programmed in Excel for you. Functions Overview Functions are simply pre-programmed formulas already provided for you in Excel which can perform calculations covering a wide range of categories including statistics, date and time arithmetic, financial calculations, lists, engineering, and more. Just like normal formulas that you create, functions must start with an equal sign. The equal sign is then followed by the name of the function (usually a descriptive name which indicates the purpose of the function). Most functions also require additional information known as arguments which are supplied to the function in brackets after the function name. Functions are therefore written as follows: =name(arguments) The arguments are quite often cell or range references that contain values that can be used in the function. For example, the commonest function is the SUM function which, as its name suggests, is used to sum or add values together. If you wanted to add all of the values in the cells from B10 to D15 you would write this function as: =SUM(B10:D15) As you can see this is much simpler than writing your own referential formula which would look like: =B10+B11+B12+B13+B14+B15+D10+D11+D12+D13+D14+D15 Imagine writing and proofing a formula where you had to add 200 cells! Typing Functions If you are familiar with the function that you need you can type it into a cell exactly the same way you type any other formula. If you are not sure if Excel has a function or you can’t quite remember how it is written you can use the Insert Function tool on the Formula Bar to assist you. When you click on this tool the Insert Function dialog box will be presented to you which lists the most recently used or common functions and also allows you to search for other functions that you might need. The Insert Function dialog box will also type the function out for you and then provide you with a further dialog box to guide you through the process of specifying the arguments that the function needs to perform its calculation. Microsoft Excel ITTraining@sgul.ac.uk Page 27 St. George’s Information Services USING THE SUM FUNCTION TO ADD One of the most used functions is the SUM function. This function allows you to add the values in a range of cells. The function is written as: =SUM(range or ranges to add). You can type the function, and then use the pointing technique to fill in the arguments. Excel then paints marquees around the cells involved helping you to track your progress. Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E710 Formulas_4.xlsx...  Click on B9 then type =sum( to start the formula  Click on B6 to point to this cell as the start, hold down the key and click on B8 Notice the relative addressing details, 3R x 1C, that appear in the tool tip…  Type ) and press to complete the function  Click on B9, then move the mouse pointer to the fill handle on the lower right corner of the cell and drag across to E9 to fill the selected range with the equivalent functions  Click on the Copy command on the Clipboard group on the Home tab  Click on B14, hold down and then click on cells B19 and B24  Release and press to paste equivalent functions into the worksheet For Your Reference… To type a sum function for a contiguous range: 1. Type =sum( 2. Select the range of cells 3. Type ) 4. Press Handy to Know…  You can also use the Sum command in the Editing group on the Home tab of the Ribbon to have Excel automatically enter a sum function based on a range of cells.  You can also type the name of a function in upper or lowercase – it is not case sensitive. 1 2 7 Microsoft Excel ITTraining@sgul.ac.uk Page 30 St. George’s Information Services COMMON ERROR MESSAGES Microsoft Excel has some in-built messages that can assist you when something goes wrong with a formula. These messages appear in the cell that contains the formula, and sometimes also other formula cells that depend upon it. The messages are always prefixed with a hash sign (#) and appear with a code. The more common error messages are listed below. A Line of Hash (#) Signs Sometimes referred to as “tramlines”, a line of hash signs usually occurs because a column is not wide enough to display the numbers in the cell or formula. Widening the column will correct this problem – you can drag the column heading until the value in the cell appears as it should. #DIV/0! This message means you are trying to divide a value by zero – this is mathematically impossible. In the example at the left we are trying to find the average number of persons per household. All is fine as long as there is a value greater than zero in cell B3 (Houses). As soon as we change this to a zero an error message appears in the formula cell (B5). To prevent the error you will need to enter a value greater than zero into cell B3, the divisor cell. #VALUE! In this message Excel is advising that something in the formula is not a value and therefore a calculation can’t be made. A close examination of the example at the left shows cell B3 contains the word “three”. Therefore the formula in cell B5 is trying to divide 192,664 (in cell B2) with a word, which doesn’t make sense. To fix the error, a value (a number) will need to be entered in cell B3. #NAME? This message appears when text is found in a formula that can’t be matched to either a legitimate function or range name. In the example to the left, the formula has been entered as =SOME(B3:B7) – there is no such function as SOME, and presumably the author should have typed =SUM(B3:B7). Microsoft Excel ITTraining@sgul.ac.uk Page 31 St. George’s Information Services PRACTICE EXERCISE Formulas And Functions Tasks: Completed: Before starting this exercise you MUST have completed all of the topics in the chapter Formulas And Functions…  Open the workbook called PE_Formulas And Functions.xlsx (it can be found in the same folder as the student files)   Create a formula that calculates the gross pay for each employee, then use a function to calculate the total of the gross pay The total for Gross Pay should appear in E14...   Create a formula that calculates the tax as being 20% of the gross pay for each employee, then create a total for the tax   Create a formula to calculate the net pay for each employee and then a total of the net pay   Create a formula that calculates the superannuation as being 8% of the gross pay for each employee, then create a total for superannuation   Use functions to determine the average, maximum and minimum values for each column, setting the number of decimal places to 2 Your worksheet should appear as shown on the following page...   Use the Save As command to save the workbook as PE_Formulas And Functions (Completed).xlsx  Microsoft Excel ITTraining@sgul.ac.uk Page 32 St. George’s Information Services UNDERSTANDING QUICK ANALYSIS The Quick Analysis tools were developed in response to the fact that users weren’t using or even aware of the more powerful analytical tools found in Excel. So Excel decided to combine Live Preview with some of these tools to create the Quick Analysis tools. The Quick Analysis Button The Quick Analysis button appears when a range is selected in a worksheet. Clicking on the button displays the Quick Analysis gallery which contains quick analysis tools that can be applied to the selected data. The tools have been organised along tabs at the top – FORMATTING, CHARTS, TOTALS, TABLES, and SPARKLINES. When you click on a tab, options specific to that tab are presented. Using Quick Analysis Tools With Live Preview Most of the Quick Analysis tools in the Quick Analysis gallery provide a Live Preview of the changes in the worksheet when you point to an option. This is very useful if you are not sure of the formatting or type of analysis you require as it provides you with a preview of what the data would look like if you selected that specific option. At the right we have selected only the totals from the worksheet shown above. We have pointed to options from the TOTALS tab (% Total and Average) and from the FORMATTING tab (Data Bars). Live Preview has either presented another row of analysed data or has formatted the selection accordingly. All of these tools are also available on the ribbon but using the Quick Analysis tools is much quicker. Microsoft Excel ITTraining@sgul.ac.uk Page 35 St. George’s Information Services QUICK TOTALS The TOTALS tab in the Quick Analysis gallery has some useful tools and options to help you build your worksheet. You can use the options to analyse data and perform alternate arithmetic operations (e.g. AVERAGE instead of SUM) or use the options to create the totals and calculations in the first place. Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E1355 Quick Analysis_3.xlsx…  Click in cell B5, hold down , then click in cell E9 to select the range B5:E9  Click on the Quick Analysis button, then click on the TOTALS tab to see the calculation options for this range  Point to Vertical Sum to see a preview of the totals for each column  Point to Horizontal Sum to see a preview of the totals for each row  Point to the other options and study the results – do not click on any at this stage  Click on Vertical Sum to create column totals  Click on the Quick Analysis button again, click on the TOTALS tab, then click on Horizontal % to see the percentages for each day of the week 3 6 For Your Reference… To create Quick Totals in a worksheet: 1. Select the range to be totalled/calculated and click on the Quick Analysis button 2. Choose the desired calculation methodology from the TOTALS tab 7 Handy to Know…  Always check any operation that performs calculations and embeds formulas for you to ensure that the correct cells and ranges are included in totals. Microsoft Excel ITTraining@sgul.ac.uk Page 36 St. George’s Information Services QUICK SPARKLINES Sparklines are mini charts that are embedded into a worksheet, usually immediately adjacent to the data. Sparklines are only relatively new in Excel and probably haven’t gained the acceptance or understanding that Microsoft would like. So, you’ll now find them in the Quick Analysis tools where you can easily implement them without too much head scratching. Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E1355 Quick Analysis_4.xlsx…  Click in cell B5, hold down , then click in cell E9 to select the range B5:E9  Click on the Quick Analysis button, then click on the SPARKLINES tab  Point to Line to display a line drawing showing trends for each row across the four weeks  Point to Column to display the trend as columns rather than a continuous line  Click on Column to add Sparklines in column F Notice that after the Sparklines have been created the SPARKLINE TOOLS tab on the ribbon is now available so that you can further enhance or modify the Sparklines 3 5 For Your Reference… To use Quick Sparklines in a worksheet: 1. Select the range to be analysed, then click on the Quick Analysis button 2. Choose the desired Sparkline from the SPARKLINES tab Handy to Know…  The Win/Loss is a special type of Sparkline that shows positives above an imaginary line and negatives below it. You need to have values range from the negative to the positive to make any good use of it. Microsoft Excel ITTraining@sgul.ac.uk Page 37 St. George’s Information Services QUICK TABLES In computer terminology a table is created when data is organised into rows and columns. You’d think then that a worksheet would be a table – but it is not in the Excel definition. In Excel a table does have columns and rows of continuous data. But it must also have headings which provide filter buttons. Creating a table is not hard, but it is much easier using Quick Tables. Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E1355 Quick Analysis_5.xlsx…  Click in any cell containing data  Hold down + , then press to select all of the non-empty cells around the current cell  Using the scroll bars, scroll to the bottom right corner of the selection, click on the Quick Analysis button, then click on the TABLES tab  Click on Table to turn the selected range into a table  Scroll across and on the drop arrow for Position to see sorting and filtering options  Click on Select All to remove the tick, then click on Effective People Leader so it appears ticked  Click on [OK] to see only those people with this position title 3 For Your Reference… To use Quick Tables to create a table: 1. Select the entire data to be used as a table 2. Click on the Quick Analysis button 3. Click on the TABLES table, then click on Table 6 7 Handy to Know…  A drawback of using Quick Tables is that all of the data must be selected first. Using the normal operation to create a table (the Table command on the INSERT tab of the ribbon) only one cell in the table needs to be selected. Microsoft Excel ITTraining@sgul.ac.uk Page 40 St. George’s Information Services THE CHARTING PROCESS Charts provide a way of seeing trends in the data in your worksheet. The charting feature in Excel is extremely flexible and powerful and allows you to create a wide range of charts from any of the Insert commands in the Charts group on the Inserting Charts The first step when creating a chart is to select the data from the worksheet that you want to chart. It is important to remember that the selected range (which can be either contiguous or non-contiguous), should include headings (e.g. names of months, countries, departments, etc). These become labels on the chart. Secondly, the selected range should not (normally) include totals as these are inserted automatically when a chart is created. The second step is to create a chart using the INSERT tab on the ribbon. You can choose a Recommended Chart where Excel analyses the selected data and suggests several possible chart layouts. Alternatively you can create the chart yourself from scratch by choosing one of the Insert commands in the Charts group. Charts that you create in Excel can be either embedded into a worksheet, or they can exist on their own sheets, known as chart sheets. Chart Sheets If you want to keep your chart separate from the data you can move the chart to its own sheet. Chart sheets make it easier and more convenient to work with your chart because you’ll see more of it on the screen – since the data is not there! Embedded Charts Charts that appear within a worksheet are known as embedded charts. A chart is really an object that sits on top of the worksheet – unlike numbers and letters, charts are not actually placed into worksheet cells. Microsoft Excel ITTraining@sgul.ac.uk Page 41 St. George’s Information Services CHOOSING THE RIGHT CHART Column, Bar These chart types, either in 2D or 3D, are used to compare values across categories. For example, they could compare the populations of different countries. Line, Area Lines in 2D or 3D are useful for showing trends such as sales or employment figures. An area chart is a line chart with the area below the line filled in. Surface The surface chart plots trends in two dimensions. You could use this to plot departmental sales figures over time. The chart then shows you the trends between departments, as well as the sales trends over time. Pie, Doughnut If you want to show proportion, such as the sales figures from different departments that make up a total, then the pie and doughnut charts are for you. The only variation between the doughnut chart and the pie chart is that the doughnut chart can display more than one series of values. Stock The stock chart type has been designed to show the stock figures for a day, and the trend over time. At its simplest, you can plot the high, low and close figures, and at its most complex, the volume, open, high, low, and close. It can be adapted to show the relationships between any five sets of values. XY (Scatter) Scatter diagrams are used to display the relationship between two variables. For example, you could research the age and price of a series of cars, and plot the values you find. You could also investigate the height and weight relationship of a group of people. Radar A radar diagram is designed to show the change in values from a central point. For example, it can be used to show mobile telephone coverage, including multiple networks and multiple measurements. A chart is far more effective at communicating results, outcomes or trends than a table of figures displaying the same information. Different chart types have been created to communicate different types of information. Some charts show simple relationships between values, while others are designed for quite technical purposes. Here is a summary of the use of different chart types. Microsoft Excel ITTraining@sgul.ac.uk Page 42 St. George’s Information Services USING A RECOMMENDED CHART If you are undecided about the best type of chart for the data you have selected to graph, then you may wish to use Excel’s Recommended Charts feature. This feature analyses your selected data and presents you with what it considers to be the best way to chart that data. Several alternatives are presented and you simply choose the one you like most. Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E1317 Charting_1.xlsx…  Click in cell A3, hold down , then click in cell G7 to select the range A3:G7  Click on the INSERT tab, then click on Recommended Charts in the Charts group The Insert Chart dialog box will display with a number of recommended chart options…  Click on each of the alternatives in the left pane to see a preview of how the chart will appear in the right pane and spend a few moments reading the descriptions  Click on Line chart (the second alternative in the left pane), then click on [OK] to embed the chart in the worksheet  Point to the top border of the chart, then click and drag the chart immediately below the data  Click in cell A1 to deselect the chart 1 You can also use the Quick Analysis tool that appears at the bottom right corner of a selected range to create a quick chart. However, this method will not allow you to preview a wide variety of charts. 2 For Your Reference… To use the Recommended Charts feature: 1. Select the data to be charted 2. Click on the INSERT tab, then click on Recommended Charts in the Charts group 3. Click on the desired chart and click on [OK] Handy to Know…  When selecting data for a chart you should include headings (e.g. names of the month, regions, etc.) but not the totals derived from the data. In the example above the names of the months and the cities are selected but the total revenue and the regional totals are not. Microsoft Excel ITTraining@sgul.ac.uk Page 45 St. George’s Information Services RESIZING A CHART Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E1317 Charting_3.xlsx...  Click on the chart to select it  Point to the sizing handle on the left border of the chart until the mouse pointer changes to a double arrow  Hold down the left mouse button and drag left until the chart appears as shown You can also resize a chart from the ribbon…  Click on the CHART TOOLS: FORMAT tab  Click on the up spinner arrow for Shape Height in the Size group until it shows 8.5 cm  Click on the up spinner arrow for Shape Width in the Size group until it shows 17 cm  Click in cell A1 to deselect the chart 3 For Your Reference… To resize a chart: 1. Select the chart, then click on and drag a sizing handle on the border of the chart, or Click on the CHART TOOLS: FORMAT tab, then click on up/down spinner arrows for Shape Height and Shape Width in the Size group There are two main ways to resize a chart if you are not satisfied with its current size. A chart that has been selected can be resized by dragging one of the sizing handles around its border. These handles appear with dots in them. You can also resize a chart using commands in the Size group on the CHART TOOLS: FORMAT tab that appears when the chart is selected. 4 Handy to Know…  If you wish to change the size of a chart quickly and easily, clicking on and dragging the resize handles is the best option whereas if you want to resize a chart to a specific size it is best to resize the chart using the tools in the Size group on the CHART TOOLS: FORMAT tab. Microsoft Excel ITTraining@sgul.ac.uk Page 46 St. George’s Information Services REPOSITIONING A CHART Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E1317 Charting_4.xlsx...  Click on the chart to select it  Point to the border of the chart until the mouse pointer changes to a four- headed arrow  Hold down the left mouse button and drag the chart below the data so that the Total Revenue row in the worksheet is visible  Click in cell A1 to deselect the chart 2 It’s unlikely that a chart embedded in the worksheet by Excel will be exactly where you would like it to be. You can easily relocate a chart to a more appropriate position by clicking on and dragging the border of the chart to the desired location. The chart obviously must be selected before it can be dragged to a new position. For Your Reference… To move a chart: 1. Click on the chart to select it 2. Move the mouse pointer to the border of the chart until the mouse pointer changes to a four-headed arrow 3. Drag the chart to a new location Handy to Know…  You can use the standard cut and paste commands to move a chart. Select the chart, click on the HOME tab, then click on Cut in the Clipboard group to copy it to the clipboard. Click in a new location and, on the HOME tab, click on Paste in the Clipboard group to paste the chart. 3 Microsoft Excel ITTraining@sgul.ac.uk Page 47 St. George’s Information Services PRINTING AN EMBEDDED CHART Try This Yourself: O p e n F il e Before starting this exercise you MUST open the file E1317 Charting_5.xlsx…  Click on the FILE tab, then click on Print to see a preview of the data and the chart Not all of the chart or data may be visible so we’ll change the orientation to landscape…  Click on Portrait Orientation in Settings then select Landscape Orientation  Click on [Print] to print the chart If you don’t have a printer connected or you don’t wish to print, click on the Back arrow to display the workbook again 1 When you print a worksheet, Excel will print whatever is in or embedded in that worksheet (including charts). This makes it easy and convenient to print both the chart and its underlying data. All you need to do is to position the chart in the appropriate location then access the print commands in the usual way. 2 For Your Reference… To print an embedded chart: 1. Click on the FILE tab, then click on Print 2. Click on [Print] Handy to Know…  If you only want to print the chart and not the data, click on the chart to select it, click on the FILE tab, then click on Print. You will notice that only the chart will appear in the preview. Microsoft Excel ITTraining@sgul.ac.uk Page 50 St. George’s Information Services CHANGING THE CHART LAYOUT Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E1317 Charting_8.xlsx...  Click on the Revenue Chart worksheet tab to see the chart, then click anywhere on the chart to select it and see the CHART TOOLS: DESIGN and CHART TOOLS: FORMAT tabs  Click on the CHART TOOLS: DESIGN tab, then click on Quick Layout in the Chart Layouts group to display a gallery of layout options  Click on Layout 3 to apply this chart layout to the chart  Repeat steps 2 and 3 to select other chart layouts and see how they appear when applied to the chart  Click on Quick Layout in the Chart Layouts group and click on Layout 5  Click on the Chart Data worksheet tab to display this worksheet 2 Excel has a gallery of chart layouts that can be applied to an existing and selected chart that is either in its own worksheet or embedded into the data worksheet. Chart layouts are the way elements of the chart are placed within the chart. Different layout options can therefore change the appearance of your chart and its readability. For Your Reference… To change the chart layout: 1. Ensure the chart or chart sheet is selected 2. Click on the CHART TOOLS: DESIGN tab, then click on Quick Layout in the Chart Layouts group 3. Select the desired layout Handy to Know…  Chart layouts are predefined themes created by Microsoft. Even if you choose one of these layouts you can still make your own modifications to the way the elements and objects are positioned and how they appear. 5 Microsoft Excel ITTraining@sgul.ac.uk Page 51 St. George’s Information Services CHANGING THE CHART STYLE Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E1317 Charting_9.xlsx...  Click on the Revenue Chart worksheet tab to see the chart, then click anywhere on the chart to select it  Click on the Chart Styles tool to the right of the chart to see a gallery of style options, as shown  Scroll through the gallery and point to each style to see how your chart will look in Live Preview  Scroll to and click on Style 9  Click on the Chart Styles tool to the right of the chart to close the gallery  Click on the Chart Data worksheet tab 2 The style of a chart refers to its colour scheme and overall appearance and can impact the clarity of the content of the chart. Choosing a predefined chart style can save valuable time and effort. Excel also makes it easy to change chart styles if you decide the style you have chosen is not appropriate. For Your Reference… To change the chart style: 1. Ensure the chart or chart sheet is selected 2. Click on the Chart Styles tool to the right of the chart 3. Click on the desired style Handy to Know…  Instead of using the Chart Styles tool to the right of the chart, you can also choose chart styles from the CHART TOOLS: DESIGN tab on the ribbon when a chart is selected. 4 Microsoft Excel ITTraining@sgul.ac.uk Page 52 St. George’s Information Services PRINTING A CHART SHEET Try This Yourself: S a m e F il e Continue using the previous file with this exercise, or open the file E1317 Charting_10.xlsx...  Click on the Revenue Chart worksheet tab  Click on the Chart Title text box, select the text, then type Revenue Chart to change the title  Repeat step 2 to change the Axis Title to Euros  Click on the FILE tab, then click on Print to see the print options and a preview of the chart No further adjustment is required here so we can go ahead and print it…  If you wish to print the chart, click on [Print] If you don’t have a printer connected or wish to save paper, click on the Back arrow to return to the worksheet…  Click on the Chart Data worksheet tab 2 For Your Reference… To print a chart sheet: 1. Click on the chart sheet tab 2. Click on the FILE tab, then click on Print 3. Click on [Print] You can print an embedded chart simply by printing the worksheet as if it is a standard worksheet. You can also print a chart sheet in exactly the same way. To print a chart sheet, the worksheet data. But the real benefit of inserting simply ensure that the chart sheet is active, then click on the FILE tab, click on Print, apply the print settings as desired and click on [Print]. Handy to Know…  When you preview a chart prior to printing, it may not appear as clearly as you would like. This is due to the screen resolution, not the chart itself. The printed version of the chart will appear clearer than the preview. 4 Microsoft Excel 2013 - Level 1 © Watsonia Publishing Page 55 Chapter 10 - Practice Exercise PRACTICE EXERCISE Creating Charts Tasks: Completed: Before starting this exercise you MUST have completed all of the topics in the chapter Creating Charts…  Open the workbook called PE_Creating Charts.xlsx (it can be found in the same folder as the student files)   Create a Clustered Column chart showing the sales of products for the months of January through to June   Drag the chart down below the data and resize it so that it is the same width as the data, keeping the proportions as far as possible   Change the chart type to 3-D Stacked Column and change the chart title to Sales The chart should appear as shown in sample A on the following page...   Create a Pie in 3-D chart of the products and their totals then place it on its own chart sheet called Product Sales   Change the Chart Title to Product Sales   Change the layout to Layout 6 The chart should appear as shown in sample B on the following page...   Print the pie chart   Use the Save As command to save the workbook as PE_Creating Charts (Completed).xlsx  Files required for exercise: PE_Creating Charts.xlsx Files/work created by student: PE_Creating Charts (Completed).xlsx, 1 printed copy of the Product Sales chart Exercise Completed:  Microsoft Excel 2013 - Level 1 © Watsonia Publishing Page 56 Chapter 10 - Practice Exercise PRACTICE EXERCISE SAMPLE Creating Charts A B Microsoft EXCEL Training Level 1 Excel Training - Level 1 Page | 4 6.3. Functions ............................................................................................................................................ 67 6.3.1. Creating a function ...................................................................................................................... 69 6.3.2. The Function Library .................................................................................................................... 71 6.3.3. The Insert Function command ..................................................................................................... 74 7. Working with Data...................................................................................................................................... 80 7.1 Freezing Panes and View Options ........................................................................................................ 80 7.2 Sorting Data........................................................................................................................................ 81 7.3 Filtering Data ...................................................................................................................................... 83 8. Working with Charts ................................................................................................................................... 84 8.1. Understanding charts ......................................................................................................................... 84 8.2. Chart layout and style ......................................................................................................................... 85 8.3. Other chart options .............................................................................................................................87 9. Printing Workbooks .................................................................................................................................... 89 9.1. Choosing a print area .......................................................................................................................... 90 9.2. Fitting and scaling content .................................................................................................................. 93 Excel Training - Level 1 Page | 5 1. Opening Excel Using Windows 7 1. Click on the Start Button. 2. In the Search Program and Files box type Excel. 3. Click on Excel 2013 from the Program results. 4. The Microsoft Excel 2013 program will open. Using Windows 8 1. Press the Windows key on the keyboard. 2. Type Excel. 3. Click on Excel 2013 under the Apps results. Using iOS 7 1. Click on Launchpad. 2. Select Microsoft Excel. 2. Getting Started When you open Excel 2013 for the first time, the Excel Start Screen will appear. From here, you'll be able to create a new workbook, choose a template, and access your recently edited workbooks. 1. From the Excel Start Screen, locate and select Blank workbook to access the Excel interface. 2. Click Open Other Workbooks to work on an existing workbook. Excel Training - Level 1 Page | 6 To set up Excel so it automatically opens a new workbook 1. Click File then Options. 2. On the General tab, under Start up options, uncheck the Show the Start screen when this application starts box. 3. The next time you start Excel, it opens a blank workbook automatically similar to older versions of Excel. 2.1. The Excel Interface After starting Excel, you will see two windows - one within the other. The outer window is the Application Window and the inner window is the Workbook Window. When maximized, the Excel Workbook Window blends in with the Application Window. After completing this module, you should be able to:  Identify the components of the Application Window.  Identify the components of the Workbook Window. Open an existing workbook Create a new workbook Excel Training - Level 1 Page | 9 To minimize and maximize the Ribbon The Ribbon is designed to respond to your current task, but you can choose to minimize it if you find that it takes up too much screen space. 1. Click the Ribbon Display Options arrow in the upper-right corner of the Ribbon. Each tab is divided into groups Click a tab to see more commands Excel Training - Level 1 Page | 10 2. Select the desired minimizing option from the drop-down menu:  Auto-hide Ribbon: Auto-hide displays your workbook in full-screen mode and completely hides the Ribbon. To show the Ribbon, click the Expand Ribbon command at the top of screen.  Show Tabs: This option hides all command groups when not in use, but tabs will remain visible. To show the Ribbon, simply click a tab.  Show Tabs and Commands: This option maximizes the Ribbon. All of the tabs and commands will be visible. This option is selected by default when you open Excel for the first time. To Customize the Ribbon in Excel 2013 You can customize the Ribbon by creating your own tabs with whichever commands you want. Commands are always housed within a group, and you can create as many groups as you want in order to keep your tab organized. If you want, you can even add commands to any of the default tabs, as long as you create a custom group in the tab. 1. Right-click the Ribbon and then select Customize the Ribbon... from the drop-down menu. 2. The Excel Options dialog box will appear. Locate and select New Tab. Click the Expand Ribbon command to show the Ribbon Ribbon is hidden and workbook appears in full screen Excel Training - Level 1 Page | 11 3. Make sure the New Group is selected, select a command, and then click Add. You can also drag commands directly into a group. 4. When you are done adding commands, click OK. The commands will be added to the Ribbon. Select commands and click Add Excel Training - Level 1 Page | 14  Zoom Control To use the Zoom control, click and drag the slider. The number to the right of the slider reflects the zoom percentage. Challenge! 1. Open Excel 2013. 2. Click through all of the tabs, and review the commands on the Ribbon. 3. Try minimizing and maximizing the Ribbon. 4. Add a command to the Quick Access toolbar. 5. Navigate to Backstage view, and open your Account settings. 6. Try switching worksheet views. 7. Close Excel (you do not have to save the workbook). Normal View Page Layout View Page Break View Excel Training - Level 1 Page | 15 2.1.2. The Workbook Window In Excel 2013, when you open up a new workbook it now contains only 1 worksheet There can be a max of 1,048,576 rows and 16,384 columns in an excel work sheet.  The Worksheet Excel files are called workbooks. Each workbook holds one or more worksheets (also known as "spreadsheets"). Whenever you create a new Excel workbook, it will contain one worksheet named Sheet1. A worksheet is a grid of columns and rows where columns are designated by letters running across the top of the worksheet and rows are designated by numbers running down the left side of the worksheet. When working with a large amount of data, you can create multiple worksheets to help organize your workbook and make it easier to find content. You can also group worksheets to quickly add information to multiple worksheets at the same time. To rename a worksheet Whenever you create a new Excel workbook, it will contain one worksheet named Sheet1. You can rename a worksheet to better reflect its content. In our example, we will create a training log organized by month. 1. Right-click the worksheet you wish to rename, then select Rename from the worksheet menu. Excel Training - Level 1 Page | 16 2. Type the desired name for the worksheet. 3. Click anywhere outside of the worksheet, or press Enter on your keyboard. The worksheet will be renamed. To insert a new worksheet 1. Locate and select the New sheet button. Click to add a new worksheet Excel Training - Level 1 Page | 19 4. The worksheet will be copied. It will have the same title as the original worksheet, as well as a version number. TIP: You can also copy a worksheet to an entirely different workbook. You can select any workbook that is currently open from the To book: drop-down menu. To move a worksheet Sometimes you may want to move a worksheet to rearrange your workbook. 1. Select the worksheet you wish to move. The cursor will become a small worksheet icon . 2. Hold and drag the mouse until a small black arrow appears above the desired location. Excel Training - Level 1 Page | 20 3. Release the mouse. The worksheet will be moved. To change the worksheet color You can change a worksheet's color to help organize your worksheets and make your workbook easier to navigate. 1. Right-click the desired worksheet, and hover the mouse over Tab Color. The Color menu will appear. 2. Select the desired color. A live preview of the new worksheet color will appear as you hover the mouse over different options. In our example, we'll choose Red. 3. The worksheet color will be changed. Excel Training - Level 1 Page | 21 The worksheet color is considerably less noticeable when the worksheet is selected. Select another worksheet to see how the color will appear when the worksheet is not selected. Challenge! 1. Open an existing Excel workbook. 2. Insert a new worksheet and rename it. 3. Delete a worksheet. 4. Move a worksheet. 5. Copy a worksheet.  The Scrolling Buttons These buttons scroll the display of sheet tabs one at a time or to display the first and last grouping of sheet tabs and are located to the left of the sheet tabs. Scroll between worksheets