1 / 48

Microsoft® Excel 2010 Always More to Learn

Microsoft® Excel 2010 Always More to Learn. Course Overview. Overview: Using Tables and AutoFilters Applying Themes Adding Sparklines Using Conditional Formatting Techniques Using the CountA Function Using Subtotals to Count Filtered Cells Saving a File as a PDF Creating Macros

tolla
Download Presentation

Microsoft® Excel 2010 Always More to Learn

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft® Excel 2010Always More to Learn

  2. Course Overview • Overview: • Using Tables and AutoFilters • Applying Themes • Adding Sparklines • Using Conditional Formatting Techniques • Using the CountA Function • Using Subtotals to Count Filtered Cells • Saving a File as a PDF • Creating Macros • Running Macros • Creating PivotTables and PivotCharts

  3. Using Tables and AutoFilters • Tablesprovide professional presentation features for displaying worksheet data. • Formatting data in a table format works best when there are not any existing background colors. • You should also be sure that there are no blank columns or rows.

  4. Using Tables and AutoFilters • Click any cell in the table range and on the Home tab in the Styles group, click the Format as Table button to display the Table Format gallery. • When you select a table style, the Format As Table dialog box opens with the range displayed.

  5. Using Tables and AutoFilters • Check to be sure that the table range is correct and click OK. The formatted table appears with AutoFilters. • Notice that the chart does not format with the table. AutoFilters

  6. Using Tables and AutoFilters • AutoFilteris a feature that lets you display only cells that meet specific criteria. • Excel has predefined filters for numbers such as Greater Than, Less Than, Above Average, and Below Average by selecting Number Filters. • To view the available options, simply click the arrow located in the column you want to filter as shown below.

  7. Table and AutoFilter Exercise • Open the Tables file. • Click in a cell in the table, such as Department. • On the Home tab, in the Styles group, click the Format as Table button. • Click on a style in the Table Format gallery, verify the range, and click OK to close the Format as Table dialog box.

  8. Table and AutoFilter Exercise • Click the AutoFilter arrow on the Qty Purchased column. • Click the Number Filters option and then select Greater Than. • Type 500 in the top right box as shown below. • Click OK. Notice that the quantities greater than 500 are displayed.

  9. Using Themes • Themes are a simple way to make your worksheets appear more professional. • With themes, you can have borders, background colors, shading, and graphic effects applied instantly to an entire workbook. • When you apply a theme to one worksheet in the workbook, each worksheet in the workbook appears with that same theme. • Several themes are available with Excel 2010, and each theme has a specific look. • Themes work best when there is already basic formatting, such as a table format, in the worksheet.

  10. Using Themes • The themes that are loaded with Excel 2010 are located in the Theme gallery as shown below:

  11. Using Themes • Shown below are two formatted worksheets in the same workbook: Customer Purchases worksheet Customer Returns worksheet

  12. Using Themes • Next, the Austin Theme will be applied to the Customer Purchases worksheet. • As you move the mouse pointer over the Themes in the Theme Gallery, the themes are displayed in the worksheet.

  13. Using Themes • When the Austin theme is applied to the Customer Purchases worksheet, this theme was automatically applied to the Customer Returns worksheet as well. Customer Purchases worksheet Customer Returns worksheet

  14. Themes • When you apply a theme to a workbook, the charts are formatted with the same theme as the worksheet data. • Theme exercise: Open the Themes workbook. • Select a cell in the data, such as a heading. • Click the Page Layout tab and in the Themes group, click a theme of your choice. • Click on each worksheet to view the themes.

  15. Adding Sparklines • A new feature introduced in Excel 2010 is the addition of sparklines. • A sparkline is a miniature chart that can be displayed in a single row or column of data. • You can also create these small charts for each row or column of data within a worksheet.Shown next are examples of sparklines.

  16. Creating Sparklines • Sparklines are displayed next to the data they represent. • To create Sparklines, you do the following steps: • Select the data that you want included in the Sparkline. • Click the Insert tab on the Ribbon and then select a type of Sparkline in the Sparkline group. For this example, Columns. • The Create Sparklines dialog box displays. Sparkline group

  17. Creating Sparklines • Sparkline exercise: Open the Sparklines workbook. • Select the range B4:B12 • Click the Insert tab and in the Sparklines group, click Column. • Type H4:H12 in the Location Range box. • Click OK to close the Create Sparklines dialog box. Sparkline group

  18. Using New Conditional Formatting Techniques • Conditional formatting applies a font, border, or pattern to worksheet cells when certain conditions exist in those cells. • For example, you might want to highlight products in a worksheet that are your top sellers. • By applying conditional formats, you can then view the cell formats to see which cells met the condition. • Various types of conditional formats are available such as font colors and cell background colors as well as icon sets, color scales, and data bars. • Examples of the new conditional formatting techniques are shown next.

  19. New Conditional Formatting Techniques • Conditional Formatting using Icon Sets: • Conditional Formatting using Data Bars: • Conditional Formatting using Color Scales:

  20. New Conditional Formatting Techniques • Conditional Formatting Exercise: Open the Conditional Formatting workbook. • Select the range I4:I18. • Click the Home tab, click the Conditional Formatting button arrow in the Styles group and then click New Rule. The New Formatting Rule dialog box opens. Verify that the Rule Type is set to Format all cells based on their values. • Click the Format Style drop-down arrow and then click Icon Sets. • Change the Type to Number. • In the Value box, type 15. Your dialog box should display as shown next.

  21. New Conditional Formatting Techniques

  22. Using COUNTA • To count cells with text in the cell instead of numbers, you use the COUNTA function. The COUNTA function can count cells if they contain text or a combination of text and numbers. • To use COUNTA, you first click in the cell where you want the count total to appear.

  23. Using COUNTA • COUNTA Exercise: Open the COUNTA workbook. • To use COUNTA, you first click in the cell where you want the count total to appear. In this example, you select D36. • Type the function =COUNTA(D5:D34) and then press Enter. The number 30, which is the count of students in the classes and is textual data, appears in the cell.

  24. Using SubTotal to Count Filtered Cells • The Subtotal function is used to add subtotals to data, but it can also be used to count, sum, or average filtered data. • When you filter data, sometimes you may want to count the number of filtered cells in a column. After the data is filtered, only the cells for the item you filtered will be displayed; the other cells will be hidden. • If you use the COUNT function, it will count all cells in the column whether they are hidden or not. • Using the Subtotal function lets you count only cells that are displayed. • The Subtotal function is =SUBTOTAL (function number, data range). The function number is a number that represents a calculation. The function number lets you control the calculation that the Subtotal function performs, such as sum, count, or average.

  25. Using SubTotal to Count Filtered Cells • A list of commonly used Subtotal function numbers and the calculations they perform is shown next.

  26. Using SubTotal to Count Filtered Cells • Subtotal Exercise: Open the Subtotal workbook. • First, you will filter the data. Click in a cell in the heading, such as A4. • On the Home tab in the Editing group, click the Sort & Filter button. • Click Filter. Notice that the AutoFilter arrows appear on the heading row. • Click the Paid AutoFilter arrow. • Click Select All to deselect the items and then click Yes to Filter by those students that have paid for the class.

  27. Using SubTotal to Count Filtered Cells • Select cell C46. • =SUBTOTAL(2, D5:D44) and press Enter. The following figures show counted cell and the formula in those cells. • Extra Learning: To display the formulas you press CTRL + ~.

  28. Saving a File as PDF • Excel 2010 allows you to save a file as a PDF file, which saves you quite a bit of time. • Prior to this feature, you would need to print a document and then scan the document as a PDF file. • Now you can not only save a file as PDF, you can save and send it at the same time.

  29. Saving a File as PDF • To save a workbook as a PDF file, you click on the File tab. • Then, click the Save & Send option on the File tab to view the options. • Click the Create PDF / XPS Document as shown below and then click the Create PDF/XPS button.

  30. Saving a File as PDF • The Publish as PDF or XPS dialog box displays as shown below. • When you click Publish, the file opens in Adobe Reader as a PDF file.

  31. Creating Macros • A macroautomates a common, repetitive task you perform in Excel, thereby saving valuable time. • Before you create a macro, is it best to understand that macros are susceptible viruses. A virus is a computer program that is designed to reproduce by copying itself and attaching to other programs in a computer. • Viruses can cause extreme damage to data on your computer. And, if a virus attaches itself to a macro, it can cause damage when you run the macro. • To help protect your data from the corruption caused by a virus hidden in a macro, you can set one of four macro security levels in Excel as shown here.

  32. Creating Macros • As a best practice, you should set the security level in Excel to Disable all macros with notification or Disable all macros except digitally signed macros. • When a workbook contains a macro, it will need to be saved as a Macro-Enabled workbook with the file extension .xlsm, which is a file type specified for macros. • You may save a workbook with the .xlsm extension before you create the macro or you can save an ordinary Excel workbook with data already entered as a macro-enabled workbook using the Save As command. • The extensions for saving an Excel workbook are shown below.

  33. Creating Macros • You will now learn how to create a macro, then you will learn how to run the macro, which means to have the macro perform the automated task. • Open the Macro workbook. • On the Ribbon, click the Developer tab and then click the RecordMacro button in the Code group. The Record Macro dialog box opens and asks for the following information.

  34. Creating Macros • In the Macro name box, type Formats. • For the shortcut key, press Shift + F. • Leave This Workbook for the stored location. • Type Add text format and cell background colors in the Description box.

  35. Creating Macros • An example of a completed Record Macro dialog box is shown below.

  36. Creating Macros • Notice that the Record Macro button changed to the Stop Recording button on the Developer tab in the Code group because Excel is now recording everything you do.

  37. Creating Macros • Here are some examples of tasks you can record in a macro: • select the range A4:A9. • click the Home tab, and then click the Bold button in the Font group. • click the Fill Color button arrow, and then choose Red, Accent 2, Lighter 40%. • select the range A12:A15. • click the Bold button in the Font group, click the Fill Color button arrow, and then choose Yellow. • select the range B4:B15. • In the Number group, click the Comma Style button . The Comma Style format will add decimal places and a comma to separate thousands. • Click the Developer tab, and then click the Stop Recording button

  38. Creating Macros • The results of the completed macro are shown below.

  39. Running Macros • To run the Macro, you simply press the shortcut keys you entered for the macro; or, • Click the Developer tab, click the Macro button in the Code group, and then click the name of the macro you wish to run. Finally, you click OK to run the macro.

  40. Enhanced Features of PivotTables and PivotCharts • You can you rearrange the data in a worksheet by creating PivotTables. • And, you can create a visual representation of this data in a PivotChart. • The PivotTable and PivotChart are linked. If a change is made to the PivotTable, the associated PivotChart changes as well. If changes are made to the PivotChart, the PivotTable reflects this change too.

  41. Enhanced Features of PivotTables and PivotCharts • Creating a basic PivotTable is easier is Excel 2010 since you view the PivotTable in the worksheet as you create it. • PivotTable Exercise: Open the PivotTable file. • Select cell A4. • Then, on the Ribbon, click the Insert tab, and click the PivotTable button in the Tables group. The Create PivotTable dialog box opens. • Verify that the correct range appears in the Table/Range text box. • To create the PivotTable in a new worksheet, select the New Worksheet option button and then click OK.

  42. Enhanced Features of PivotTables and PivotCharts • The PivotTable Field List dialog box appears. • Drag fields from field list to Layout area.

  43. Enhanced Features of PivotTables and PivotCharts • Drag the fields to the Layout area as shown below.

  44. Enhanced Features of PivotTables and PivotCharts • After you create the PivotTable, you can sort and filter the data to provide additional ways to analyze it. • The sort and filter options are displayed when you click the Column Labels down arrow or the Row Labels arrow on the PivotTable.

  45. Enhanced Features of PivotTables and PivotCharts • A Slicer may also be added to a PivotTable. • It is a visual control that looks like a note pad. A Slicer acts like a filter, but it is easier to use. • Clicking selections on the Slicer lets you filter your data in a PivotTable. • An example of a Slicer is shown below.

  46. Enhanced Features of PivotTables and PivotCharts • Adding Slicer to a PivotTable is an easy task. • First, select any cell in the PivotTable. Then, click the Options tab and click Insert Slicer in the Sort & Filter group. The Insert Slicers dialog box opens. • Click the check boxes for the fields you want to appear in the Slicer and then click OK.

  47. Enhanced Features of PivotTables and PivotCharts • After the Slicer appears in the worksheet, you may drag it next to the PivotTable or to any convenient location. • There are several Slicer styles that are available, which enhance the appearance of the Slicer. More button

  48. Questions • Are there any questions you would like answered? • Please feel free to ask!

More Related