As a data analyst, working with data is the core of your job, and Microsoft Excel is one of the most widely used tools for data analysis. Mastering these essential skills will not only help you work more efficiently but also improve your overall data analysis process. I. Creating Tables in Excel A. Importance: Organized data: Tables provide a structured format to organize your data, making it easier to read and understand. Consistency: Tables maintain consistent formatting and ensure that any new data entered will automatically adopt the same formatting. Data integrity: Tables reduce the likelihood of errors by providing drop-down lists, data validation, and the ability to add calculated columns. B. Steps to create a table: Select the data range. Press 'Ctrl+T' or click on 'Table' from the 'Insert' tab. Check 'My table has headers' if your data has headers, and click 'OK'. II. Sorting Tables in Excel A. Importance: Improved readability: Sorting data makes it easier to read, comprehend, and compare data Pattern identification: Sorting helps identify trends, patterns, and outliers, enabling you to make data-driven decisions. Data preparation: Sorting is an essential step in data cleansing and preparation, making your analysis more accurate and efficient. B. Steps to sort a table: Select a cell within the column you want to sort. Go to the 'Data' tab and choose either 'Sort A to Z' (ascending) or 'Sort Z to A' (descending). For custom sorting, click on 'Sort' and set your desired sorting criteria. III. Querying Tables in Excel A. Importance: Data extraction: Querying allows you to extract specific data subsets based on your criteria, making it easier to focus on relevant information. Data analysis: Querying helps you answer specific questions about your data and perform more targeted analysis. Time-saving: Querying reduces the time spent on manual filtering, enabling you to analyze larger datasets more efficiently. B. Querying methods in Excel: Filters: Apply filters to your table to display only rows that meet specific criteria. Conditional formatting: Visually highlight cells based on specified conditions to draw attention to relevant data. Module 5: Working with Multiple Worksheets and Workbooks Shelly Cashman: Microsoft Excel 2019 Module 5: Working with Multiple Worksheets and Workbooks © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 1 Objectives (1 of 2) Format a consolidated worksheet Fill using a linear series Use date, time, and rounding functions Apply a custom format code Create a new cell style Copy a worksheet Drill to add data to multiple worksheets at the same time Select and deselect sheet combinations © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 2 Objectives (2 of 2) Enter formulas that use 3-D cell references Use the Paste gallery Format a 3-D pie chart with an exploded slice and lead lines Save individual worksheets as separate workbook files View and hide multiple workbooks Consolidate data by linking separate workbooks © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 3 Project—Consolidated Expenses Worksheet © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 5-1: Display of consolidated worksheet. 4 Creating the Consolidated Worksheet To Apply a Theme Display the Page Layout tab, click the Themes button and scroll to the desired theme © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 5-3: Display of Page Layout tab with Themes button. 5 Fill Series To Create Linear Series Enter a value in the desired cell Select the desired range, including the desired cell Drag the AutoFill handle on the lower right of the cell to the desired location © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 6 Date, Time, and Round Functions To Enter Formulas Using the ROUND Function Select the desired cell. Type the =round formula and then click the Enter box in the formula bar to display the formula and the resulting value in the select cell Drag the fill handle on the lower right of the cell to the desired location Click the AutoSum button on the Home tab, select the desired cell range, and then press the Enter button to sum the column © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 5-15: Display of ROUND function with Enter button and result of round function displayed in cell G5. 7 Format Codes (1 of 2) Table 5-5 Format Symbols in Format Codes Format SymbolExample of Symbol in CodeDescription # (number sign)###.##Serves as a digit placeholder. If the value in a cell has more digits to the right of the decimal point than number signs in the format, Excel rounds the number. All digits to the left of the decimal point are displayed. 0 (zero)0.00Works like a number sign (#), except that if the number is less than 1, Excel displays a 0 in the ones place. . (period)#0.00Ensures Excel will display a decimal point in the number. The placement of zeros determines how many digits appear to the left and right of the decimal point. % (percent)0.00%Displays numbers as percentages of 100. Excel multiplies the value of the cell by 100 and displays a percent sign after the number. , (comma)#,##0.00Displays a comma as a thousands separator. ( )#0.00;(#0.00)Displays parentheses around negative numbers. $, +, or –$#,##0.00; ($#,##0.00)Displays a floating sign ($, +, or –). * (asterisk)$*##0.00Displays a fixed sign ($, +, or –) to the left, followed by spaces until the first significant digit. [color]#.##;[Red]#.##Displays the characters in the cell in the designated color. In the example, positive numbers appear in the default color, and negative numbers appear in red. ” ” (quotation marks)$0.00 “Surplus”; $-0.00 “Shortage”Displays text along with numbers entered in a cell. _ (underscore)#,##0.00_)Adds a space. When followed by a parentheses, positive numbers will align correctly with parenthetical negative numbers. © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Table 5-5: Format Symbols in Format Codes. 8 Format Codes (2 of 2) To Create a Custom Format Code Select the range of cells and right-click any of the selected ranges to display a shortcut menu, and then click Format Cells to display the Format Cells dialog box If necessary, click the Number tab and then click Custom in the Category list Delete the word General in the Type box, and then enter the desired format code Click OK to display the numbers using the custom format code © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 9 Creating a Cell Style (1 of 2) To Create a New Cell Style Click the Cell Styles button to display the Cell Styles gallery Click “New Cell Style” in the Cell Styles gallery to display the Style dialog box Type the desired name for the new style Click the Format button to display the Format Cells dialog box and select desired formats Click OK to close the Format Cells dialog box Click OK to add create the new style © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 10 Creating a Cell Style (2 of 2) To Apply a New Style Select the desired cell(s) and then click the Cell Styles button to display the Cell Styles gallery Click the name of the new style to assign the style to the selected cell(s) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 5-29: Display of Home tab, Cell Styles button, and new custom cell style. 11 Working with Multiple Worksheets (1 of 3) To Add a Worksheet to a Workbook Click the New sheet button at the bottom of the window to add a new worksheet to a workbook To Copy and Paste from One Worksheet to Another Click the sheet tab for the sheet containing the data to copy Click the Select All button to select the entire worksheet and then click the Copy button to copy the contents of the worksheet Press ENTER to copy the data from the Office Clipboard to the selected sheet © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 12 Working with Multiple Worksheets (2 of 3) To Copy a Worksheet Using a Shortcut Menu Right-click the desired sheet tab to display the shortcut menu Click “Move or Copy” to display the Move or Copy dialog box Click the desired location and then click to place a check mark in the “Create a copy” check box Click OK to add a copy of the worksheet to the workbook To Copy a Worksheet Using CTRL Select a sheet CTRL+drag the selected sheet tab to a location to the right of the other sheet tabs. Do not release the drag Release the drag to create the worksheet copy © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 13 Working with Multiple Worksheets (3 of 3) To Drill an Entry through Worksheets Right-click Sheet1 and then click “Select All Sheets” Type the entry in the desired cell and then press the DOWN ARROW key to change sample data to the actual value Enter the remaining entry changes in the other cells and then select a blank cell to select the same cell in all of the selected workbooks Right-click the Sheet1 tab and then click Ungroup Sheets Click through the sheet tabs in use to verify that all are identical © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 14 Referencing Cells Across Worksheets (1 of 2) To Enter a 3-D Reference Select the desired cell and then click the AutoSum button to display the SUM function Click the desired sheet tab to display the worksheet, and then click the same cell to select the first portion of the argument for the SUM function SHIFT+click a new desired sheet tab to select the ending range of the argument for the SUM function Click the Enter box in the formula bar to enter the SUM function with the 3-D references in the selected cell © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 15 Referencing Cells Across Worksheets (2 of 2) To Use the Paste Gallery With the desired cell active, click the Copy button to copy the selected cell to the Office Clipboard Select the desired range and then click the Paste arrow to display the Paste gallery Click the Formulas button in the Paste gallery to copy the SUM function to the desired range replicating the 3-D references Press ESC to clear the marquee © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for