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 Symbol Example of Symbol in Code Description
# (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.00 Works like a number sign (#), except that if the number is less than 1, Excel displays a 0 in the ones place.
. (period) #0.00 Ensures 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.00 Displays 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.00 Displays 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