MS Excel Training Two

1. MS Excel Training Two

2. Section One: Excel 2007 Section Goals Create a new workbook. Enter text and numbers. Edit text and numbers. Insert and delete columns and rows.

3. The Ribbon • The band at the top of the Excel 2007 window is the Ribbon. The Ribbon is made up of different tabs. Each tab is related to specific kinds of work that people do in Excel. You click the tabs at the top of the Ribbon to see the different commands on each tab. The Home tab, the first tab on the left, contains the everyday commands that people use the most. • Commands are organized in small related groups. For example, commands to edit cells are grouped together in the Editing group, and commands to work with cells are in the Cells group.

4.  The Ribbon spans the top of Excel.  Related commands on the Ribbon are organized in groups. The Ribbon

5. More Commands, when you need them… • The commands on the Ribbon are the ones you use the most. Instead of showing every command all the time, Excel 2007 shows some commands when you may need them, in response to an action you take. For example, if you don't have a chart in your worksheet, the commands to work with charts aren't necessary. But after you create a chart, the Chart Tools appear, with three tabs: Design, Layout, and Format. On these tabs, you'll find the commands you need to work with the chart. The Ribbon responds to your action. • Use the Design tab to change the chart type or to move the chart location; the Layout tab to change chart titles or other chart elements; and the Format tab to add fill colors or to change line styles. When you complete the chart, click outside the chart area. The Chart Tools go away. To get them back, click inside the chart. Then the tabs reappear.

6. Don't worry if you don't see all the commands you need at all times. Take the first steps. Then the commands you need will be at hand. Create a chart by clicking a button on the Insert tab in the Charts group. Then the Chart Tools tabs become available: Design, Layout, and Format.

7. Even More Options • When you see this arrow (called the Dialog Box Launcher) in the lower-right corner of a group, there are more options available for the group. Click the arrow, and you'll see a dialog box or a task pane. • For example, on the Home tab, in the Font group, you have all the commands that are used the most to make font changes: commands to change the font, to change the size, and to make the font bold, italic, or underlined.

8. If you want more options, such as superscript, click the arrow to the right of Font, and you'll get the Format Cells dialog box, which has superscript and other options related to fonts.  Click the arrow in the Font group.  The Format Cells dialog box opens. Click the arrow at the bottom of a group to get more options if you need them.

9. What About Keyboard Shortcuts? If you rely on the keyboard more than the mouse, you'll want to know about keyboard shortcuts in Excel 2007. The Ribbon design comes with new shortcuts which brings two big advantages over previous versions: • Shortcuts for every single button on the Ribbon. • Shortcuts that often require fewer keys. Using the new shortcuts • The new shortcuts also have a new name: Key Tips. You press ALT to make the Key Tips appear. • You'll see Key Tips for all Ribbon tabs, all commands on the tabs, the Quick Access Toolbar, and the Microsoft Office Button. Press the key for the tab you want to display. This makes all the Key Tip badges for that tab's buttons appear. Then, press the key for the button you want.

10.  Press ALT to make the Key Tips appear.  Then press H to select the Home tab.  Press A, then C in the Alignment group to center the selected text. You can use Key Tips to center text in Excel, for example.

11. Old Keyboard Shortcuts • What about the old keyboard shortcuts?    Keyboard shortcuts of old that begin with CTRL are all still intact, and you can use them like you always have. For example, the shortcut CTRL+C still copies something to the clipboard, and the shortcut CTRL+V still pastes something from the clipboard.

12. A New View • Not only the Ribbon is new in Excel 2007. Page Layout view is new too. If you have worked in Print Layout view in Microsoft Office Word, you'll be glad to see Excel with similar advantages. • To see the new view, click Page Layout View on the View toolbar on the bottom right of the window. Or click the View tab on the Ribbon, and then click Page Layout View in the Workbook Views group. • In Page Layout view there are page margins at the top, sides, and bottom of the worksheet, and a bit of blue space between worksheets. Rulers at the top and side help you adjust margins. You can turn the rulers on and off as you need them (click Ruler in the Show/Hide group on the View tab). • With this new view, you don't need print preview to make adjustments to your worksheet before you print. You'll see more about that in the next lesson. It's easy to add headers and footers in Page Layout view. When you type in the new header and footer area at the top or bottom of a page, the Design tab opens with all the commands you need to create your headers and footers. • You can see each sheet in a workbook in the view that works best for that sheet. Just select a view on the View toolbar, or in the Workbook Views group on the View tab, for each worksheet. Normal view and Page Break preview are both there.

13. Tip    If you like Page Layout view, but you'd prefer a little less white space in the margins, you can hide some of that white space.  Column headings.  Row headings.  Margin rulers. The new Page Layout view in Excel.

14. Section Review • You can customize Excel 2007 by adding commands to the Quick Access Toolbar. • If you don't like all the white space in Page Layout view, how do you change to Normal view? • The best thing to do to get started in Excel 2007 is to go to the _____. • Some commands appear only when you need them. True or False?

15. Section Two: Pivot Tables Section Goals: Learn how to build a PivotTable report. Change the layout of a PivotTable. Find the PivotTable fields. Understand the PivotTable Field List. Remove a field from a PivotTable report. Sorting fields in a PivotTable report. Explain what a plus sign (+) beside a field in the report means.

16. Introduction • Your worksheet has lots of data, but do you know what the numbers mean? Does your data answer all your questions? PivotTable reports can help to analyze numerical data and answer questions about it. • Imagine an Excel worksheet of sales figures with hundreds or thousands of rows of data. The worksheet lays out all the data about salespeople in two countries and how much they sold on individual days. But it's a lot of data to deal with — listed in row after row and divided into multiple columns. How can you get information out of the worksheet? How can you make sense out of all of this data? • Who sold the most overall? Who sold the most per quarter or per year? Which country has the most sales? You can get answers to all these questions with PivotTable reports — it's like turning a mob into a marching band. A PivotTable report turns all that data into small, concise reports that tell you exactly what you need to know.

17. Rows and rows of data summarized in a PivotTable report.

18. Review Source Data • Before you start to work with a PivotTable report, take a look at your Excel worksheet to make sure it is well prepared for the report. When you create a PivotTable report, each column of your source data becomes a field that you can use in the report. Fields summarize multiple rows of information from the source data. • The names of the fields for the report come from the column titles in your source data. Be sure that you have names for each column across the first row of the worksheet in the source data. In the picture below, the column titles Country, Salesperson, OrderAmount, Order Date, and OrderID will become field names. When you create a report, which you'll do in the practice at the end of the lesson, you'll know, for example, that the Salesperson field represents the Salesperson data from the worksheet. • The remaining rows below the headings should contain similar items in the same column. For example, text should be in one column, numbers in another column, and dates in another column. In other words, a column that contains numbers should not contain text, and so on. • Finally, there should be no empty columns within the data that you are using for the PivotTable report. We also recommend that there be no empty rows; for example, blank rows that are used to separate one block of data from another should be removed.

19. A tidy Excel worksheet.

20. Getting Started • When the data is ready, place the cursor anywhere in the data. That will include all the worksheet data in the report. Or select just the data you want to use in the report. • Then, on the Insert tab, in the Tables group, click PivotTable, and then click PivotTable again. The Create PivotTable dialog box opens. • Select a table or range is already selected for you. The Table/Range box shows the range of the selected data. New Worksheet is also selected for you as the place where the report will be placed (you can click Existing Worksheet if you don't want the report placed in a new worksheet).

21. The Create PivotTable dialog box.

22. PivotTable Basics • On one side is the layout area ready for the PivotTable report, and on the other side is the PivotTable Field List. This list shows the column titles from the source data. As mentioned earlier, each title is a field: Country, Salesperson, and so on. • You create a PivotTable report by moving any of the fields to the layout area for the PivotTable report. You do this either by selecting the check box next to the field name, or by right-clicking a field name and selecting a location to move the field to. If you have worked with PivotTable reports before, you may wonder if you can still drag fields to build a report. You can, and you'll see how at the end of the lesson. • Tip    If you click outside of the layout area (of a PivotTable report), the PivotTable Field List goes away. To get the field list back, click inside the PivotTable layout area or report.

23.  The layout area for the PivotTable report.  The PivotTable Field List. This is what you see in the new worksheet after you close the Create PivotTable dialog box.

24. Build a PivotTable Report • Now you are ready to build the PivotTable report. The fields you select for the report depend on what you want to know. • Let's start with finding out how much each salesperson has sold. To get the answer, you need data about the salespeople. So select the check box in the PivotTable Field List next to the Salesperson field. You also need data about how much they have sold, so select the check box next to the Order Amount field. Notice that you don't have to use all the fields on the field list to build a report. • When you select a field, Excel places it in a default area of the layout for you. You can move the field to another area if you want to. For example, if you want a field to be in the column area instead of the row area.

25. Build a PivotTable Report (cont’d) • The data in the Salesperson field (the salespeople's names), which does not contain numbers, is automatically displayed as rows on the left side of the report. The data in the Order Amount field, which does contain numbers, correctly shows up in an area to the right. • The heading over the salesperson data says "Row Labels" above the field. The heading over the order amounts says "Sum of Order Amount"; the "Sum of" part of the heading is because Excel uses the Sum function to add up fields with numbers. • Note that it doesn't matter whether you select the check box next to the Salesperson field before or after the Order Amount field. Excel will automatically put them in the right place every time. Fields without numbers will land on the left, fields with numbers will land on the right, regardless of the order in which you select them. • That's it. With just two mouse clicks you know how much each salesperson sold. By the way, you could stop with just one question answered. You can use a PivotTable report as a fast way to get the answer to just one or two questions. The report doesn't have to be complex to be useful.

26. Tip    Don't worry about building a report incorrectly. Excel makes it easy to try things out, to see how data looks in different areas of the report. If a report isn't how you want it at first, it doesn't take long at all to lay out data another way, to move pieces around to your satisfaction, or even to start over again if you want. PivotTable Dialogue

27. Sales By Country • Now you know how much each salesperson sold. But the source data lays out data about salespeople in two countries, Canada and the United States of America (USA). So another question you might ask is: What are the sales amounts for each salesperson by country? • To get the answer, you can add the Country field to the PivotTable report as a report filter. You use a report filter to focus on a subset of data in the report, often a product line, a time span, or a geographic region. • By using the Country field as a report filter, you can see a separate report for Canada or the USA, or you can see sales for both countries together.

28. To add this field as a report filter, right-click the Country field in the PivotTable Field List and then click Add to Report Filter. The new Country report filter is added to the top of the report. The arrow by the Country field shows (All), and you see the data for both countries. To see just the data for either Canada or the USA, click the arrow and select either country. To see the data for both countries again, click the arrow and then click (All). Tip    To remove a field from a report, clear the check box beside the field name in the PivotTable Field List. To remove all the fields from the report so that you can start over, on the Ribbon, on the Options tab, in the Actions group, click the arrow on the Clear button, and then select Clear All. Sales By Country

29. Sales By Date • The original source data has a column of Order Date information, so there is an Order Date field on the PivotTable Field List. That means you can get the answer to another question: What are the sales by date for each salesperson? To get the answer, select the check box next to the Order Date field to add the field to the report. • The Order Date field is automatically added on the left, in row label orientation. That's because the field does not contain numbers (dates may look like numbers, but they are formatted as dates, not numbers). Because the Order Date field is the second non-numerical field to be added to the report, it is nested within the Salesperson field, indented to the right.

30. Now the report shows sales for each salesperson by individual date, but that's a lot of data to view at one time. You can easily get this data into a more manageable view by grouping the daily data into months, quarters, or years. To group the dates, click a date in the report. Then on the Options tab, in the Groupgroup, click Group Field. In the Grouping dialog box, select Quarters, which seems like a good solution here, and then click OK. Now you see the sales data grouped into four quarters for each salesperson. Sales By Date (cont’d)

31. Pivot the Report • Though the PivotTable report has answered your questions, it takes a little work to read the entire report; you have to scroll down the page to see all the data. • You can pivot the report to get a different view. To do that, you move a field from the Row Labels area to the column area of the report (called Column Labels), which is an area of the layout you haven't used before. When you pivot a report, you transpose the vertical or horizontal view of a field, moving rows to the column area or moving columns to the row area. It's easy to do. • To pivot this report, right-click one of the "Qtr" rows, point to Move, and then click Move "Order Date" to Columns. This will move the entire Order Date field from the Row Label area to the Column Label area of the report.

32. Now the names of the salespeople are all together, and it says Column Labels above the first quarter of the sales data, which is now laid out in columns across the report. Also, the grand totals for each quarter are at the bottom of each column. Instead of scrolling down the page to see the data, you can see it all at a glance. If you want to pivot the report back to the original view, right-click one of the "Qtr" headings, and then select Move "Order Date" to Rows. That will move the Order Date field back to the row area of the report. Pivoting the Data

33. Where did Drag-and-Drop Go? • As mentioned earlier in the lesson, if you prefer to build a PivotTable report by using the drag-and-drop method, as you could in previous versions of Excel, there is still a way to do that. • There are four boxes at the bottom of the PivotTable Field List. The boxes are called Report Filter, Row Labels, Column Labels, and Values. You can drag fields to this area. • Normally in Excel 2007 you add fields to the report layout from the field list either by selecting check boxes next to the field names, or by right-clicking the field and selecting a location from a menu. As you do so, the fields are automatically placed in the layout, and they are simultaneously placed in the appropriate box at the bottom of the list.

34. For example, as the Salesperson field is placed in the Row Labels area of the report layout, the Salesperson field name is also displayed in the Row Labels box at the bottom of the field list. But if you prefer the drag-and-drop method, you can just drag fields from the top of the field list into the boxes at the bottom of the field list. The label at the top of each box tells you into which area of the report the field should go. For example, if you drag the Order Amount field to the Values box, that field will be in the Values area of the report. You can also drag the fields between the boxes to change locations in the report layout area, and you can drag fields out of the boxes to remove them from the report. Drag-and-drop method.

35. Section Review • After you build a PivotTable report, you can't change the layout. • What are PivotTable fields? • In the PivotTable Field List, you can tell which fields are already displayed on the report. • How do you remove a field from a PivotTable report? • The first field you add to a PivotTable report that does not contain numbers will automatically be added in the _____ part of the report. • What does it mean when you see a plus sign (+) beside a field, there is more detail about the field in the report.

36. Section Three: More Pivot Tables Section Goals: Apply a filter to a field. Know how to clear one filter. Clear a filter by clicking in the PivotTable Field List. Be able to add filters by clicking the arrow next to Row Labels or Column Labels. Add a report filter to a PivotTable report. Use the Date Filters command PivotTable report.

37. See Only the Data You Want • Imagine that you have opened a PivotTable report for products sold by Adventure Works, a sporting goods company. The report nicely summarizes more than 6,000 rows of Excel worksheet data, showing you what all that data means. You can see, for example, the yearly sales totals for each product over a three-year period, and there is also a grand total of sales for each product.

38. Now you want to look at specific details. Adventure Works sells products ranging from water bottles to road bikes and vests. You'd like to see the sales amounts for road bikes only. Then you'd like to see which road bikes sold the most. And then you want to see how selected road bikes sold during a certain period of time. You can see exactly what you want by filtering the PivotTable report data. PivotTable report before filtering.

39. Filter to see one product out of many • You want to see just the sales data for road bikes, and hide all the other data for now. To filter the report, click the arrow next to Row Labels. You click there because road bikes are displayed in the row area of the report. When you click that arrow, a menu appears with the Select field box at the top for you to use in selecting where to apply the filter. • On this menu is a list that shows all the rows in whatever field you select. Looking at the items in the list is a way to verify that you've selected the correct field to filter. In this example, the Product Category field, which includes Road Bikes, is the one you want.

40. To filter the report, clear the (Select All) check box in the list. That clears the check boxes next to every item in the list. Then select the check box next to Road Bikes. Now the PivotTable report shows only data for road bikes. The other data is not changed, but for now it does not appear. Tip    It's not always easy to tell if data has been filtered or not just by looking at it. To remind you that this report is filtered, a filter icon appears on the arrow that you clicked to begin setting the filter: . There's also a filter icon in the PivotTable Field List next to the field name that the filter is applied to. Choose the filter you want.

41. Narrow the filter to see a single item • Now you have set a filter, and the report shows only the sales totals for road bikes. But Adventure Works sells many different types of road bikes, and you would like to see the sales totals for just one type, the Road-350-W. • You could filter the report to a single type by using the method shown in the last section. In the Select field box, you would choose Product Name instead of Product Category. In the list of product names, you would select the check boxes for the Road-350-W road bike.

42. But a different method can save you time, by refining the filter you have already set. In the filtered report, select the cells with Road-350-W data in them. Then right-click, point to Filter, and click Keep Only Selected Items. Now just the Road-350-W data is displayed. The new filter hides all the other road bike product names and data that were in the report before. Select Cells and Right-Click.

43. Set a value filter to view products by specifying amounts • Suppose you want to see which road bike models have sales totals of $100,000 or more. How do you tell Excel to select those rows and hide the others? You use a value filter. • First, you filter to view only road bikes, using the method you saw in the first animation. Then, to set the value filter, click the arrow on the filter icon next to Row Labels. In the Select field box, choose Product Name. You filter the Product Name field because that field contains every individual road bike model. • Next, point to Value Filters. This filter reads the data and selects rows with cells that match your criteria. Click Greater Than Or Equal To, and in the Value Filter dialog box, enter 100,000 in the empty box.

44. The report contains 38 road bike models, of which 13 have total sales of $100,000 or more, and only those now appear. To change the dollar amount you want to filter for, point to Value Filters and repeat the process with a different value. You can imagine how filtering like this lets you analyze your data in all sorts of ways with comparatively little effort. Value Filter.

45. Set a date filter to see data for a selected time • Finally, suppose you want to see how well road bikes sold during a specific year, month, or other period of time. By setting a filter, you can tell the report what time period you are interested in, and temporarily hide data for all other periods. • To filter on a specific year is easy. To see the data for 2011 only, click the arrow next to Column Labels. You click there because dates are displayed in the column area of the report. In the list that appears, clear the check box next to (Select All) and then select the check box next to 2011. Excel hides the 2009 and 2010 figures. It's just that fast.

46. Next, say that you want to see how well road bikes sold during a particular month in 2011. To specify a time period for the date filter, click the arrow next to Column Labels. Point to Date Filters. Click Between. In the Date Filter dialog box, type 11/8/2011 in the first empty box. In the and box, type 12/8/2011. The report displays data for road bikes for the specified month only. Set Date Filter.

47. Remove filters • Do you want to see the hidden data again, to look at the big picture? You can go slow and easy by removing filters one at a time, or you can quickly remove all filters at once. • The key to removing filters one at a time is using the filter icon, which appears in two different places: on the PivotTable report , and in the PivotTable Field List . • To remove all filters at once, you use commands on the Ribbon at the top of the window.

48. Remove a filter in the PivotTable report • To remove a filter from a specific field, click the filter icon wherever the filtered field appears in the report, either on Row Labels or on Column Labels. Then click Clear Filter From <Field Name>. Or select the check box next to (Select All) to make all data in that field visible. • If you do not see the Clear Filter From command for the filtered field: • Be sure that you have selected the correct area of the report to clear the filter from: either rows or columns. • Is the correct field name in the Select field box? You see that box when you click the filter icon. The field name in that box must match the name of the field you want to clear the filter from. If the field name in the box is incorrect, select the correct field from the list that appears when you click the arrow next to the box.

49. Remove a filter in the PivotTable Field List • Move the cursor over the filter icon next to the field name you want to remove the filter from. Click the arrow that appears, and then click Clear Filter From <Field Name>. Or select the check box next to (Select All) to make all data in the field visible.

50. Remove all filters at one time • At the top of the window, on the Ribbon, click the Options tab under PivotTable Tools. In the Actions group, click Clear, and then click Clear Filters. Be sure you really want to do this before you undo all your selections.