BUS 1350 Exam 2 Review for Baylor
University
Excel Worksheet Management
Creating and Managing Worksheet Groups
To create a worksheet group of non-adjacent sheets, press and hold CTRL while clicking
each sheet tab.
A single workbook can have multiple windows, allowing for different views of the same
data.
To ungroup a worksheet group, click the sheet tab of any worksheet in the group.
Cell References and Formulas
A 3-D cell reference to cell C20 in the Monday worksheet is written as Monday!C20.
The absolute 3-D cell reference for cell C20 in the Monday worksheet is Monday!
20.
To reference cell C20 across multiple worksheets (Monday through Friday), use
Monday:Friday!C20.
External References
An external reference to cell C20 in the Final Report worksheet located in the Annual
Statement.xlsx workbook is written as '[Annual Report.xlsx]Final Report'!C20.
For a cell D10 in the Sunday worksheet of the Weekly Report.xlsx workbook, the
reference is '[C:\Documents\Reports\Weekly Report.xlsx]Sunday'!D10.
Excel Functions and Formulas
Lookup Functions
The VLOOKUP function retrieves the letter grade for a score of 83 from the GradeScale
table using the formula =VLOOKUP(83, GradeScale, 2).
, The INDEX function retrieves the value located in the fifth row and second column of a
specified range with =INDEX(A10:C50, 5, 2).
The MATCH function finds the index number of a cell equal to 'Carter' in the range
E1:E50 using =MATCH('Carter', E1:E50, 0).
Counting and Summarizing Data
The COUNTIF function counts the number of cells in the range B1:B50 that equal 'B' with
=COUNTIF(B1:B50, 'B').
The AVERAGEIF function calculates the average of cells in C1:C50 where the adjacent cell
in B1:B50 equals 'B' using =AVERAGEIF(C1:C50, 'B', B1:B50).
The SUMIFS function calculates the sum of values in D1:D50 where adjacent values in
A1:A50 equal 'Senior' and in B1:B50 equal 'B' with =SUMIFS(D1:D50, A1:A50, 'Senior',
B1:B50, 'B').
PivotTables and Data Analysis
Understanding PivotTables
A PivotTable is an interactive table that summarizes data in a concise format, allowing
for easy data analysis.
The four primary areas of a PivotTable are Rows, Columns, Values, and Filters, which
help organize and analyze data effectively.
Default statistics for non-numeric data in the Values area is count, while for numeric
data, it is sum.
Filtering and Grouping Data
PivotTables can be filtered by adding fields to the Filters area or using filter buttons in
row and column areas.
When a date field is placed in a row or column area, Excel automatically groups the
dates into new fields for month, quarter, and year.
Slicers can be applied to multiple PivotTables by selecting the slicer and using the Report
Connections button.
Data Structure and Management
Fields and Records
University
Excel Worksheet Management
Creating and Managing Worksheet Groups
To create a worksheet group of non-adjacent sheets, press and hold CTRL while clicking
each sheet tab.
A single workbook can have multiple windows, allowing for different views of the same
data.
To ungroup a worksheet group, click the sheet tab of any worksheet in the group.
Cell References and Formulas
A 3-D cell reference to cell C20 in the Monday worksheet is written as Monday!C20.
The absolute 3-D cell reference for cell C20 in the Monday worksheet is Monday!
20.
To reference cell C20 across multiple worksheets (Monday through Friday), use
Monday:Friday!C20.
External References
An external reference to cell C20 in the Final Report worksheet located in the Annual
Statement.xlsx workbook is written as '[Annual Report.xlsx]Final Report'!C20.
For a cell D10 in the Sunday worksheet of the Weekly Report.xlsx workbook, the
reference is '[C:\Documents\Reports\Weekly Report.xlsx]Sunday'!D10.
Excel Functions and Formulas
Lookup Functions
The VLOOKUP function retrieves the letter grade for a score of 83 from the GradeScale
table using the formula =VLOOKUP(83, GradeScale, 2).
, The INDEX function retrieves the value located in the fifth row and second column of a
specified range with =INDEX(A10:C50, 5, 2).
The MATCH function finds the index number of a cell equal to 'Carter' in the range
E1:E50 using =MATCH('Carter', E1:E50, 0).
Counting and Summarizing Data
The COUNTIF function counts the number of cells in the range B1:B50 that equal 'B' with
=COUNTIF(B1:B50, 'B').
The AVERAGEIF function calculates the average of cells in C1:C50 where the adjacent cell
in B1:B50 equals 'B' using =AVERAGEIF(C1:C50, 'B', B1:B50).
The SUMIFS function calculates the sum of values in D1:D50 where adjacent values in
A1:A50 equal 'Senior' and in B1:B50 equal 'B' with =SUMIFS(D1:D50, A1:A50, 'Senior',
B1:B50, 'B').
PivotTables and Data Analysis
Understanding PivotTables
A PivotTable is an interactive table that summarizes data in a concise format, allowing
for easy data analysis.
The four primary areas of a PivotTable are Rows, Columns, Values, and Filters, which
help organize and analyze data effectively.
Default statistics for non-numeric data in the Values area is count, while for numeric
data, it is sum.
Filtering and Grouping Data
PivotTables can be filtered by adding fields to the Filters area or using filter buttons in
row and column areas.
When a date field is placed in a row or column area, Excel automatically groups the
dates into new fields for month, quarter, and year.
Slicers can be applied to multiple PivotTables by selecting the slicer and using the Report
Connections button.
Data Structure and Management
Fields and Records
