Excel Level 1: Mastering the Basics

1. Excel Level 1 Jeffrey A. Richards August 2016

2. Excel Level 1 Agenda • Part One – The Excel Environment • Introducing the Ribbon • The File Tab & Quick Access Toolbar • The Command Tabs • Part Two – Entering Data • Entering Data into Cells * • Basic Cell Formatting * • Quickly Formatting Tables * • Finishing Touches: Summarizing and Embellishing ** • Part Three – Working with Data and Tables • Entering Data More Efficiently * • Moving Data within a Workbook * • Finding and Replacing Data * • Correcting and Expanding Upon Data* • Part Four – Finishing Your Workbook • Adding Headers or Footers * • Printing and Previewing * • Setting Print Areas * * • Customizing Excel • Part Five - Data Filtering • Limiting Data that Appears on your Screen: Filtering * * • AutoCalculate* • Limiting Data In: Data Validation * *

3. Upcoming Excel Classes at North Seattle College • Excel Level 2 • 8/13/2016 and 8/16/2016 • Sorting & Organizing Data • Charts and Graphs • Formulas 101 • Collaboration & Protection of Workbooks • Excel Level 3 • 8/20/2016 • Macros • Alternative Data Sets • Conditional Functions • Brief Intro to Pivots • Getting Certified in Excel Advanced Level 4 Classes for Excel • Excel Formulas and Functions • Saturday 10/22/2016 • Personal Finance Functions • Text Functions • Date and Time Functions • Statistics and Array Functions • Logical and Look-up Functions • Excel PivotTable Data Crunching • Saturday 11/05/2016 • Creating PivotTables and Basic Formatting • Customizing PivotTables • Performing Calculations in Pivots • Remapping Source Data and Reverse Pivots • PivotCharts – the Best of Both Worlds

4. Part One - The Excel Environment • For most people, Excel has always been one of those programs you grow into. Unless your job responsibilities demanded a detailed working knowledge of spreadsheets right from the start, your earliest experiences with Excel might have been creating simple Financial documents, maybe to help track personal spending, a project at work, or in response to a request from a manager. • Over the years since its “birth” Excel has evolved from a basic spreadsheet tool to a major business application that enables information workers to create, analyze, consolidate, report on, and share critical information that effects the entire business. • Like Word, Excel’s biggest challenge is to offer powerful, sophisticated tools in an accessible way that enables both experts and novices to find what they need easily and produce professional-quality, accurate, and insightful results.

5. The Excel Environment continued Introducing the Ribbon – Three basic components • Command Tabs – The Lifecycle of your Document • Group – Organized by task within the Command Tabs • Commands – Buttons within each Group that execute your tasks Command Tabs Groups At the left side of the Ribbon… Note the File tab and the Quick Access Toolbar Commands

6. The Excel Environment continued The File Tab • Leads to the Backstage View • Document lifecycle pane and contextual command pane • Save and Send options • Excel Options • The Quick Access Toolbar • Save, Undo, and Redo • Customize it The Quick Access Toolbar comes standard with all Office applications using the Ribbon. At the top of your Excel window, you will see three familiar tools: Save, Undo, and Redo. You can add any tool you want to the Quick Access toolbar by right clicking on the tool and selecting “Add to Quick Access Toolbar” from the shortcut menu. You might, for example, add the Shapes icon. The Quick Access Toolbar travels with you from application to application.

7. The Excel Environment continued Command Tabs = Stages of creation of a spreadsheet • Home tab: Clipboard, fonts, cell alignment, cell and number styles and formats, edit, search, and sort your data • Insert tab: Adding objects to your worksheets, like tables, charts, illustrations, links, text boxes, headers and footers, WordArt, and signature lines • Page Layout Tab: Offers all the things related to setting up the worksheet, including themes, margins, print area, whether or not you view your gridlines, and placement and scale of objects you inserted. • Formulas Tab: Includes the Function Wizard and Function Library, the commands you need for working with named cells, the commands you need for formula auditing, and calculation options. • Data Tab: Offers commands for getting external data, managing the connections to external links, sorting and filtering your data, removing duplicates, validating and consolidating your data, and grouping and ungrouping cells. • Review Tab: Tools for document checking (spelling, thesaurus, etc.), adding cell comments, and sharing with others for review (commenting, tracking changes, protecting, etc.) • View Tab: Provides the commands needed for choosing different workbook views, hiding and re-displaying parts of your worksheet (gridlines, ruler, formula bar, etc.), magnifying or reducing the display, and working with the worksheet window(s).

8. Part Two - Entering Data Entering Data into Cells start • Please type the text in the screenshot under the word “start” (to the right) into a blank tab in the Excel Level 1 Demo Workbook while we talk about cells. • Basics of Cells: • Cell references are a combinations of letters (column) and numbers (rows) • The Active Cell is framed • Complete a cell entry in one of three ways: • Checkmark button in the formula bar • Enter or Tab • Click any other cell • Truncated cells: Cells in columns and rows can be widened automatically or manually using the double-headed arrow. • You can also control cell width and row height by selecting columns/rows and right clicking, then choosing the appropriate option from the shortcut menu. • Cell entries which are too large for their widths can also be accommodated by the Merge and Center command or the Wrap Text command. • Rows and columns can be easily added or removed from your spreadsheet using the buttons in the Cells group on the Home tab. end

9. Entering Data continued Basic Cell Formatting start • Let’s now add some values to our cells… • Basics of Cell Formatting (Home tab, Font, Alignment, and Numbers groups) • Cells can be quickly formatted by adding borders and other effects, including: • Gridlines • Alignment (Center, Right or Left) • Font style and size • Italics, Bold, Underline • Backgrounds (called Fill) • Font coloring • We can also specify quickly the way Excel show us values, by formatting the numbers end

10. Entering Data continued Quickly Formatting Tables • Let’s Undo the manual formatting we added and get back to an unformatted table. • Applying and Manipulating Quick Table Formatting (Home tab  Styles group  Format as Table button  from the gallery: Table Style Medium 19). • You can avoid the cumbersome process of inputting your own formatting each time by using one of the pre-set Table styles • Some new concepts here: • Galleries • Contextual Command Tabs • Format Painter start end

11. Entering Data continued Finishing Touches: Summarizing and Embellishing start • Using the table we just quick formatted, let’s now put the finishing touches on… • Finishing Touches • You can quickly sum numbers by selecting the cells to sum and then pressing the AutoSum button. The results cell is the cell immediately following your selected cells. This works both horizontally and vertically. • You can remove Excel’s natural gridlines and make changes to the Quick Table format: • Make Changes to Fonts • Change the Table Style • Remove the Filter • Add Table Effects, like • Borders with Colors • Fill Effects • Backgrounds end

12. Entering Data continued • self-paced skills practice • You have been asked to help your friend Roger. After years of working with stocks and bonds, Roger created a seminar designed to educate the public on portfolio investment strategies. He offers a discount for attendees who choose to take his seminar multiple times, but he does not have a tracking system in place so he can catalog his students. Not being proficient with Excel himself, he has asked you to create a spreadsheet for him. • Open a blank spreadsheet. You decide to include the following column headings, starting in cell B2: • First Name • Last Name • Address • City • State • You decide to format the worksheet. Make the following changes: • Change the font for the entire worksheet to Arial 10 point • Change the column widths to 15 point and center the column headers • Draw a bottom border below the column headings and turn them light yellow • Change the formatting in column J (the Cost column) to an accounting format, reduce the decimals to 0 • Enter the following records in the rows to show what the worksheet will look like when populated: • Widen column D (the Address column) automatically to accommodate the longest entry. • Add two new blank rows at the top of the spreadsheet and then add a title above the table that looks like this: • Zip Code • Phone Number • Date of Seminar • Cost • Date of Payment

13. Part Three – Working with Data and Tables Entering Data More Efficiently • Once you have created your workbook, you generally begin populating it with data, right away. The simplest way to do that is to click a cell and begin typing in values or text. This is a method that works very well when you are entering only a few pieces of data. But if you are entering long sequences or a series of values, there are some better tools to help you do this efficiently. • Copy and Paste: Use the Cut or Copy commands in the Clipboard group on the Home tab to quickly move a cell to the clipboard, which can be easily copied to a different cell using the Paste button. More on this in the next section. • AutoComplete: Excel detects when a value you are entering is similar to previously entered values. Excel automatically completes the cell for you using the similar value, which you are free to keep or delete. • AutoFill: When you enter the first element in a recognized series, you can grab the fill handle in the lower right corner of the cell and stretch the series as far as you need. You can fill backwards and forwards, horizontally or vertically. Common examples are days of the week and months of the year. • FillSeries: Similar to AutoFill, this technique allows you to enter two values in a series and then use the fill handle of both cells (still selected) to automatically populate the next values in the series. Typing 3 and then 6 in adjacent cells, for example, and pulling the fill handle of the selected cells will populate the next cell with a 9. • AutoFill Copy: This is exactly the same as AutoFill, except in this case, you are using the fill handle to copy, rather than auto-populate a series. For example, if instead of January, February, March, April, you wanted to have January populate into all the cells, you simply hold down the [CTRL] key while dragging the fill handle. • Pick from a Dropdown List: This technique allows you to fill in the active cell with values from the cells adjacent to it, by selecting the value from a list of options. Simply right click in the empty cell, select “Pick from Dropdown List” from the shortcut menu, and a list of existing values from the cell’s column appears. Click on the value you want.

14. Working w/ Data & Tables continued Moving Data Within a Workbook: Copy and Paste • You can move to a specific cell in lots of ways: • Easiest: just click the cell you want to make it the active cell. • It is outlined in black and its contents (if any) appear in the formula bar • [CTRL] + End – jump to the last cell in your table • [CRTL] + Home – jump to cell A1 • Use the Go To command • Home tab, Editing group, Find & Select button • Go To… • Enter the Cell Reference in the Reference text box Paste into Source Formatting • Paste Values • Paste Values & Number Formatting Paste but Keep Origin Formatting • Paste • Paste but Keep Source Formatting • Paste Values & Source Formatting

15. Working w/ Data & Tables continued self-paced skills practice: copying, cutting, and pasting data In this exercise, you will be using the Self Paced Skills Practice Workbook 1, located in your personal file you created at the beginning of this class. Open the workbook and On the Count worksheet, select cells B2:D2. On the Home tab, in the Clipboard group, click the Copy button. Excel copies the contents of cells B2:D2 to the Clipboard. (**Keyboard Shortcut: Press Ctrl+C to copy worksheet contents to the Clipboard.) On the tab bar, click the Sales tab to display that worksheet. Select cell B2. On the Home tab, in the Clipboard group, click the Paste button’s arrow, point to the first icon in the Paste group, and then click the Keep Source Formatting icon (the final icon in the first row of the Paste gallery.) Excel displays how the data would look if you pasted the copied values without formatting, and then pastes the header values into cells B2:D2, retaining the original cells’ formatting. Right-click the column header of column I, and then click Cut. Excel outlines column I with a marquee. Right-click the header of column E, and then, under Paste Options, click Paste. Excel pastes the contents of column I into column E. (**Keyboard Shortcut: Press Ctrl+V to paste worksheet contents exactly as they appear in the original cell.) Your Sales tab worksheet should now look like the below. Close the workbook without saving it.

16. Working w/ Data & Tables continued Finding and Replacing Data Excel worksheets can contain more than a million rows of data, so it is unlikely that you will have the time to move through a worksheet a row at a time to locate the data you want to find. You can locate data on an Excel worksheet by using the Find and Replace dialog box, which has two tabs that enable you to search for cells that have particular values. Using the controls on the Find tab locates the data you specify, while using the Replace tab allows you to substitute one value for another. • The basic dialog box includes a text box asking you what you want to Find and what you want to Replace. You then have the option of Finding/Replacing all instances of the data or moving through the instances one at a time. • When you need more control over the search for data to find or replace, you can click the Options button to expand the Find and Replace dialog box. This allows you to: • Use the Format dialog box to specify what format you want Excel to search for (and replace with) • Match the case (capitalization requirement: if checked, then “Cat” in the Find What field would not return cells with the word “cat”) • Exact Match – shuts off the partial text function and forces Excel to find exactly the same value as in the Find What field (in this case, “Cat” does not match “Catherine”) • Search Within the entire workbook or just this worksheet • Search by rows or by columns • Look in cell formulas or values to find the match you want Expand the dialog box by clicking the Options button

17. Working w/ Data & Tables continued self-paced skills practice: find and replace In this exercise, you will be using the Self Paced Skills Practice Workbook 2, located in your personal file you created at the beginning of this class. Open the workbook and If necessary, click the Time Summary sheet tab. The Time Summary worksheet is displayed. On the Home tab, in the Editing group, click Find & Select, and then click Find. The Find And Replace dialog box opens with the Find tab displayed. (**Keyboard Shortcut: Press Ctrl+F to display the Find tab of the Find And Replace dialog box.) In the Find what field, type 114. Click Find Next. Excel highlights cell B16, which contains the value 114. Delete the value in the Find what field, and then click the Options button. The Find And Replace dialog box expands to display additional search options. Click Format... The Find Format dialog box opens. Click the Font tab. The Font page is displayed. In the Font style list, click Italic, then click OK. The Find Format dialog box closes. Click Find Next. Excel highlights cell D25. Click Close. The Find And Replace dialog box closes. On the tab bar, click the Customer Summary sheet tab. The Customer Summary worksheet is displayed. On the Home tab, in the Editing group, click Find & Select, and then click Replace. The Find And Replace dialog box opens with the Replace tab displayed. (**Keyboard Shortcut Press Ctrl+H to display the Replace tab of the Find And Replace dialog box.) Click the Format arrow to the right of the Find what field, and then in the list, click Clear at the bottom. The format displayed next to the Find What field disappears when you click OK. In the Find what field, type Contoso. In the Replace with field, type Northwind Traders. Click Replace All. A message box appears, indicating that Excel made three replacements (the Contoso entries in cells C4, C8, and C12). Click OK to close the message box. Click Close. The Find And Replace dialog box closes. Close the workbook without saving it.

18. Working w/ Data & Tables continued Correcting and Expanding Upon Data • After you enter your data, you should take the time to check and correct it, especially because Excel does not have the same AutoCorrect and “as-you-go” spell checking tools that other Office applications do. You can make sure that the text is spelled correctly using the Spell Checker. When Excel encounters a word it does not recognize, it highlights the word and offers suggestions, representing its best guess of the correct word. You can then accept the suggestion, ignore the misspelling, or edit the cell directly. • Review tab, Proofing group, Spelling button If you are not sure of your word choice, or if you use a word that is almost but not quite right for your meaning, you can check for alternative words by using the Thesaurus. A number of other research tools are also available, including Insights, which uses Wikipedia, Bing images, and web searches. Finally, you can use the Translate tool to look up the cell contents in other languages. • Review tab, Proofing group / Insights group / Language group

19. Working w/ Data & Tables continued self-paced skills practice: correcting and expanding upon data In this exercise, you will be using the Self Paced Skills Practice Workbook 3, located in your personal file you created at the beginning of this class. Open the workbook and 1. On the Review tab, in the Proofing group, click Spelling. The Spelling dialog box opens with the first misspelled word – “shiped“ – in the worksheet displayed in the Not In Dictionary field. Verify that the word “shipped” is highlighted in the Suggestions pane, and then click Change. Excel corrects the word and displays the next questioned word: “withn.” Click OK to close the message box. Click cell B6 (the word “Overnight”). On the Review tab, in the Proofing group, click Thesaurus. The Research task pane opens and displays a list of synonyms for the word Overnight. On the Review tab, in the Language group, click Translate. The Research task pane displays the translation tools. If necessary, in the From list, click English (U.S.). In the To list, click Spanish (International Sort). The Research task pane displays Spanish words and phrases that mean overnight - adverbio: travel por la noche. Close the workbookwithoutsavingit. Click Change. Excel corrects the word and displays the next questioned word: TwoDayGro. Click Add to Dictionary. Excel adds the word to the dictionary and displays the next questioned word: ThreeDayGro. Click Add to Dictionary. Excel adds the word to the dictionary. If necessary, click Close to close the Spelling dialog box. The Spelling dialog box closes, and a message box appears, indicating that the spelling check is complete for the worksheet.

20. Part Four – Finishing Your Workbook Adding Headers and Footers • When you print your document out for public consumption, you might want information such as the date or page number available at the top or bottom of each page. You can manually enter it into your spreadsheet, but that will be a problem if you add more rows later or want to update the date: You will manually have to re-enter or move the information. An easier way is to just add a header or footer. • This is done on the Insert tab, Text group, Header & Footer button. When you display your worksheet’s headers or footers, you go into Page Layout mode. This is a new feature which allows you to view your document in Print Preview while still being able to make edits. Excel divides its headers and footers into three sections (left, middle, right). • In the Design Contextual tab, you can add a prefabricated Header or Footer to your document using the Header or the Footer buttons on the left of the Ribbon. Or, you can click in the section of the header you want to manipulate and add • your own text. You can further • design your own Header or • Footer by customizing each • section using the controls in the • Header and Footer elements of • the Design contextual tab. • Starting with Excel 2002, you were able to add a graphic to a Header or Footer, such as a company logo. When you add a picture to your Header or Footer, the Format Picture button becomes available to allow you to open a dialog box and edit your graphic.

21. Finishing your Workbook continued self-paced skills practice: working with headers and footers In this exercise, you will be using the Self Paced Skills Practice Workbook 4, located in your personal file you created at the beginning of this class. Open the workbook and On the Insert tab, in the Text group, click Header & Footer. Excel displays your workbook in Page Layout view. In the middle header section, type Q1 2011, and then press Enter. On the Design contextual tab, in the Header & Footer Elements group, click File Name. Excel adds the &[File] code to the header. To the right of the &[File] code, type a comma, and then press the Spacebar. On the Design contextual tab, in the Header & Footer Elements group, click Current Date. Excel changes the contents of the middle header section to &[File], &[Date]. Press Tab. Excel highlights the right header section; the workbook name and current date appear in the middle header section. On the Design contextual tab, in the Navigation group, click Go to Footer. Excel highlights the right footer section. Click the middle footer section. On the Design contextual tab, in the Header & Footer Elements group, click Picture. The Insert Picture dialog box opens. Navigate to your personal folder, and then double-click ConsolidatedMessenger.png. The code &[Picture] appears in the middle footer section. Click any worksheet cell above the footer. Excel displays the worksheet as it will be printed. Click the image in the footer and then, on the Design contextual tab, click Format Picture. The Format Picture dialog box opens. Click the Size tab if the Size page is not already displayed. In the Scale area of the dialog box, in the Height field, type 80%, and then press Enter. The Format Picture dialog box closes. Click any worksheet cell above the footer. Excel displays the newly formatted picture. Close the workbook without saving it.

22. Finishing Your Workbook continued Printing and Previewing • Once you have finished your preparations, you might be ready to print it out. In the Page Layout mode, you can change the properties to make sure that the worksheet displays all of the information. • You can change the margins (boundaries of the different sections of the printed page) • in the Page Layout tab. Excel comes loaded with Normal, Wide, and Narrow • margins. If you want more control over the boundaries of the printed page, you can • click on Custom Margins. • Another issue with printing worksheets is that the data in worksheets tends to be • wider horizontally than a standard piece of paper. You can change the Orientation of • the worksheet from Portrait to Landscape to accommodate more of your table • on the printed page. • If that does not work, you can use the Scale-to-Fit controls to • force the table to fit on a single printed page. • Likewise, you can control where Excel puts in page breaks. This is done by going to the View tab and • changing the workbook view to Page Break Preview. The blue lines represent page breaks. You can insert • your own page breaks by right clicking the column or row header and selecting “Insert Page Break” from • the shortcut menu. Excel then inserts a solid blue line and forces the contents to shrink to the size needed • to be on a page the size you have specified. Excel also changes the remaining page breaks to accommodate • the one you just entered. You can now: • Grab the page breaks and drag them wider or more narrow • Delete them • Insert additional manual page breaks • You will want to take a look at your document in Print Preview before printing it.

23. Finishing Your Workbook continued Setting Print Areas • There are a number of other ways to influence the manner in which your • worksheet prints, as well. • Print Row and Column titles on every page • Page Layout tab  Page Setup group  Print Titles button • On the Sheet tab of the Page Setup dialog box, in the Print Titles • area, click in the Rows to repeat at top box and then click the collapse • button and, in the worksheet, select the row(s) containing • the column titles you want to print on each page. • Re-expand the dialog box and repeat for the letters of any columns • you want repeated as row headers, or just click OK. • Print Row Numbers and Column Letters • Page Layout tab  Sheet Options group  • Headings print checkbox • Print the Gridlines • Page Layout tab  Sheet Options group  • Gridlines print checkbox • Define the Print Area by selecting it and setting it • Page Layout tab  Page Setup group  • Print Area button  Set Print Area

24. Finishing your Workbook continued self-paced skills practice: preparing worksheets for printing In this exercise, you will be using the Self Paced Skills Practice Workbook 5, located in your personal file you created at the beginning of this class. Open the workbook and While displaying the JanFeb worksheet, click the File tab and then click Print. The workbook appears in the Backstage view. In the Backstage view, click the Orientation button, and then click Landscape Orientation. Excel reorients the worksheet. You note that the data still does not appear on a single page. In the Backstage view, click the Scaling button (which is defaulted to “No Scaling”), and then click Custom Scaling Options. The Page tab of the Page Setup dialog box opens. In the Adjust to field, type 80%, and then press Enter. Excel resizes your worksheet. You note that while the full January table is now on one page, but so is the top portion of the February table. You decide to insert a page break. On the ribbon, click the Page Layout tab. Excel displays the JanFeb worksheet and the Page Layout tab. Click the row header for row 38. Excel highlights row 38. On the Page Layout tab, in the Page Setup group, click Breaks, and then click Insert Page Break. Excel sets a horizontal page break above row 38. On the tab bar, click the MarJun sheet tab. The MarJun worksheet appears. On the Page Layout tab, in the Page Setup group, click Margins, and then click Wide. Excel applies wide margins to the worksheet, which you can see as A vertical dotted line between the March and April tables. 8. On the Page Layout tab, click the Page Setup dialog box launcher. The Page Setup dialog box opens. Click the Sheet tab. In the Page order area, click Over, then down and click OK. This will allow the worksheet to print out in chronological order by month, instead of March, May, April, and June. Click the File tab, then the print command. Use the scroll bar at the far right hand side to move through the print preview of the MarJun worksheet. Close the workbook without saving it.

25. Finishing your Workbook continued self-paced skills practice: getting ready to print In this exercise, you will be using the Self Paced Skills Practice Workbook 6, located in your personal file you created at the beginning of this class. Open the workbook and Display the Northwind worksheet and notice that the attainment calculation column contains errors, because some of the Methods of sending packages were not targeted with a quota. Since you cannot divide by 0, the attainment in those cells diplays errors. On the Page Layout tab, click the Page Setup dialog launcher. The Page Setup dialog box opens. Click the Sheet tab. The Sheet page is displayed. In the Cell errors as list, click <blank>. Click OK. Hold down the Ctrl key and then, on the tab bar, click the Summary sheet tab. Excel selects the Summary and Northwind worksheets. Click the File tab, and then click Print. The Print page of the Backstage view is displayed. In the Settings area, verify that the Print Active Sheets option is selected. Use the scrollbar at the far right to scroll down from the Summary worksheet to the Northwind worksheet to see that the errors have been replaced with blank cells in the printing Click any ribbon tab to cancel printing, and close the workbook without saving it.

26. Finishing Your Workbook continued Customizing Excel • How you use Excel depends on your personal working style and the type of data collections you manage. The Excel product team at Microsoft spent a lot of time interviewing customers, observing how different organizations use the application, and tried to set up the User Interface so that you do not have to change it in order to work effectively. However, if you find that you need to change the program window to better meet your needs or style, you certainly can. Some simple things you can do: • Zoom in and out. This is similar to a camera’s zoom process to increase the size of an object, at the expense of the expanse of an object. There are (as is often the case) several ways to do this: • Use the Zoom bar at the bottom right of your window and “drag” • Double click on the zoom percentage near the Zoom bar to open the • Zoom dialog box (10% to 100%) • Go to the View tab, Zoom group, and click the Zoom button to open the • Zoom dialog box • Freeze Panes. Sometimes your worksheet gets so long that it gets confusing to • try to look at data that is too far down the sheet without the column headings. You • can use the View tab to freeze the panes to keep the column headings always visible. • Hide or show the Ribbon. Sometimes to maximize your workspace, you want to • hide the Ribbon, at least temporarily. You do that by double-clicking on any command tab • Add or delete buttons to/from the Quick Access toolbar (as we learned earlier) • Open and display multiple Excel workbooks at the same time. • You can arrange them in ways that makes it easy for you to work with • them. You can even use the New Window button to display multiple • copies of the same workbook so you can work on different worksheets • at the same time.

27. Part Five - Data Filtering • Limiting Data In and Data Out • Making good business decisions requires that you are able to zero in on specific data. This is referred to as “limiting data out.” For example: • 10 busiest days of the month • Underperforming products or salespeople • Ability to perform calculations on the subsets of the data, such as “percent of total” • Limiting Data In is the opposite. To eliminate errors, you might want to restrict what data is allowed to be entered into a cell. • Data errors can be eliminated by entering rules to restrict cell values • For example, values too small or large; entering text in a cell that requires a number • Limiting Data That Appears on Your Screen – Filtering • On the Home Tab, to the far right, Sort & Filter • Select Filter to add an Auto-Filter to table headings • You will see the drop down arrows which allow filtering • Select a temporary subset of the data • Find & Replace, Calculations, Sorting available • Apply Multiple Filters • Knowing when a filter is on (funnel icon)

28. Data Filtering continued self-paced skills practice: data filtering (two page exercise) In this exercise, you will be using the Self Paced Skills Practice Workbook 7, located in your personal file you created at the beginning of this class. The ByRoute tab in this workbook is a list of exceptions that have occurred in the deliveries from a package company like UPS or FedEx. Open the workbook and On the ByRoute worksheet, click any cell in the cell range B2:F27. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter. A filter arrow appears in each column’s header cell. 3. Click the Date column filter arrow and then, from the menu that appears, clear the March check box. Excel removes the check from the March check box and changes the state of the Select All and 2010 check boxes to indicate that some items within those categories have been filtered. Click OK. Excel hides all rows that contain a date from the month of March. 4. Click the Center column filter arrow and then, from the menu that appears, clear the Select All check box. Excel clears all the check boxes in the list. Select the Midwest check box, and then click OK. Excel displays only the five exceptions that occurred in the Midwest distribution center during the month of April.

29. Data Filtering continued self-paced skills practice: data filtering continued 5. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear. Excel clears all active filters but leaves the filter arrows in place. Click the Route column header’s filter arrow, and then type RT9 in the Search box. The filter list displays only those routes with an identifier that includes the characters RT9. Click OK. Excel applies the filter, displaying the four exceptions that occurred on routes with identifiers that contain the string RT9. Click the MarchDailyCount sheet tab. The MarchDailyCount worksheet appears. Click any cell in the table. Click the Exceptions column filter arrow, point to Number Filters, and then click Top 10. The Top 10 AutoFilter dialog box opens. In the middle field, type 5. Click OK. Excel displays the table rows that contain the five highest values in the Exceptions column. Click the Exceptions column filter arrow, and then click Clear Filter from “Exceptions”. Excel removes the filter. Click the Date column filter arrow, point to Date Filters, and then click Custom Filter. The Custom AutoFilter dialog box opens. In the upper-left list, click is after or equal to. In the upper-right list, click 3/8/2010. In the lower-left list, click is before or equal to. In the lower-right list, click 3/14/2010. Click OK. Because you left the And option selected, Excel displays all table rows that contain a date from 3/8/2010 to 3/14/2010, inclusive. On the Quick Access Toolbar, click the Undo button to remove your filter. Excel restores the table to its unfiltered state. 12. Close the workbook without saving it.

30. Data Filtering continued • AutoCalculate • In the Status Bar • Right Click to customize the feedback provided in AutoCalculate • Count shows the number of cell that have data; Numerical Count shows number of cells with numbers / dates • Drawback: Data does not exist anywhere in the spreadsheet itself • Limiting Data In: Data Validation • Part of creating efficient, easy-to-use worksheets is doing what you can to ensure that the data entered is as accurate as possible • While you cannot catch every typographical or transcription error, you can set up a validation rule to make sure that the data in a cell meets certain standards • Steps: • Select the cells you want to restrict • Data tab, Data Tools group, Data Validation button • Settings tab in the Data Validation dialog • Select “Allow” criteria, set parameters • If you choose a list, be sure to specify values • elsewhere • Create screen tip to help the user input • accurate data • Create the error alert to warn or • block inaccurate data • Circle Invalid Data • Clear the cell restrictions in the Data • Validation dialog box (Clear All button)

31. Data Filtering continued self-paced skills practice: data validation In this exercise, you will be using the Self Paced Skills Practice Workbook 8, located in your personal file you created at the beginning of this class. The first tab in this workbook is a list of customers with their details and their credit limits. You want to set the Limit cells so that they have to have a whole number with a maximum value of $25,000. Open the workbook and Select the cell range J4:J7. Cell J7 is currently blank, but you will add a value to it later in this exercise. On the Data tab, in the Data Tools group, click Data Validation. The Data Validation dialog box opens and displays the Settings page. In the Allow list, click Whole Number. Boxes labeled Minimum and Maximum appear below the Data box. In the Data list, click less than or equal to. The Minimum box disappears. In the Maximum box, type 25000. Clear the Ignore blank check box. Click the Input Message tab. The Input Message page is displayed. In the Title box, type Enter Limit. In the Input Message box, type Please enter the customer’s credit limit, omitting the dollar sign and any commas. Click the Error Alert tab. The Error Alert page is displayed. In the Style list, click Stop. The icon that appears on your message box is now the Stop icon, if it was not already. In the Title box, type Error, and then click OK. Click cell J7. A ScreenTip with the title “Enter Limit” and the text “Please enter the customer’s credit limit, omitting the dollar sign and any commas.” appears near cell J7 Type 25001, and press Enter. A stop box with the title Error opens. Leaving the Error Message box blank in step 9 causes Excel to use its default message. Click Cancel. The error box closes. Click cell J7 again. Cell J7 becomes the active cell, and the ScreenTip reappears. Type 25000, and press Enter. Excel accepts your input. On the Data tab, in the Data Tools group, click the Data Validation arrow and then, in the list, click Circle Invalid Data. A red circle appears around the value in cell J4. In the Data Validation list, click Clear Validation Circles. The red circle around the value in cell J4 disappears. Close the workbook without saving it.