Chapter 1
Use excel to do quantitative analysis (numerical analysis)
Know what rows, columns, etc. are
What are the things you want to keep in mind when creating a workbook
(planning, etc.)?
Three different data types used in excel: Dates, Numbers, Text
Why is it important to know the difference between those
On first tab create documentation tab telling people how to use it, what its
about, etc.
Introducing Excel
Spreadsheet: collection of text and numbers laid out in rectangular grid (used
in business for budgeting, inventory management, decision making)
What-if-analysis: when you change one or more values in a spreadsheet and
assess the affect those changes have on the calculated values
Use excel to store data, generate reports, analyze data
Workbook can have two types of sheets:
o Worksheet: contains grid of rows and columns where user enters data
o Chart sheet: contains Excel chart providing visual representation of
worksheet data
Having multiple sheets allow you to better organize data in the workbook
Cell reference: the column and row location identifying each cell in the
worksheet (ex. B6)
Can move directly to a specific cell using the “Go To” dialog box or by typing
in a cell reference in the Name box
Planning a Workbook
Should develop a plan before entering data into workbook
Use a planning analysis sheet:
1. What problems do I want to solve? The answer defines the goal or
purpose of the workbook.
2. What data do I need? The answer defines the type of data that you
need to collect and enter into the workbook.
3. What calculations do I need to enter? The answer defines the formulas
you need to apply to the data you’ve collected and entered.
4. What form should my solution take? The answer defines the
appearance of the workbook content and how it should be presented
to others.
Entering Text, Numbers and Dates
Text data: any combination of letters, numbers and symbols that form words
and sentences
Number data: any numerical value that can be used in a mathematical
calculation Date and Time data: formats for date and time values (left-aligned)
AutoComplete: helps make entering repetitive text easier (does not work
with dates or numbers)
To make two lines of text in a cell press Alt key while pressing Enter
Date as numerical value: number of days between date and January 0, 1900
Storing dates/times as numerical values lets excel perform date/time
calculations (ex. Determining elapsed time between two dates)
Working with Columns and Rows
To make cell content easier to read, resize columns and rows in worksheet
Default column width: 8.43 standard-sized characters (64 pixels)
Default row height: 15 points (20 pixels)
Can define specific column width/row height using Format command
When insert new column, has same width as one to its left
When insert row, has same height as row above it
Clearing: removes data from worksheet leaving blank cells
Deleting: removes data and cells from worksheet
Working with Cells and Ranges
Range reference: indicates location and size of a cell range
Adjacent ranges: upper-left and lower-right cells in rectangle (ex. A1:G5)
Non-adjacent range: separate each adjacent range by semi-colon (ex.
A1:A5;F1:G5)
Drag and drop: select range and drag it to new location
Cutting: places cell contents on to Clipboard so can paste to new location
When insert cells, existing cells shift down when selected range is wider than
it is long, shift right when longer than it is wide
Working with Formulas
Operators: combine different values resulting in single value
Arithmetic operators: perform addition, subtraction, multiplication, division
and exponentiation
Formulas contain cell references so you can change value without having to
modify formula
Order of precedence: set of predefined rules used to determine sequence in
which operators applied in calculation (BEDMAS)
Can copy formulas down a row to save time and avoid potential mistakes in
retyping same formula over and over
Introducing Functions
Function: named operation that returns a value (used to simplify formulas)
Less likely to make a mistake (ex. =SUM(F1:F10))
Avoid putting data values within formulas, make an outside cell reference Break up formulas to show other people all the sums calculated in separate
places instead of one formula so they can better understand final result
AutoSum can quickly generate:
o Sum of values in column/row (SUM)
o Average value in column/row (AVERAGE)
o Total count of numeric values in column/row (COUNT)
o Minimum value in column/row (MIN)
o Maximum value in column/row (MAX)
Editing Worksheet Content
Use Find & Select button to replace certain things in workbook with different
things
Search string: string of characters you need to locate to change
Replacement string: new text characters that replace the previous characters
Previewing and Printing a Workbook
Normal view: shows contents of worksheet
Page Layout view: shows how worksheet will appear when printed
Page Break Preview: shows location of different page breaks within
worksheet (can control what content appears on what page)
Can change worksheet from portrait to landscape orientation to print entire
worksheet on one page
Formula view: view of worksheet contents that displays formulas instead of
resulting values (use Ctrl + ‘)
Scaling: reduces width and height of printout to fit number of pages by
shrinking text size
Chapter 5
No questions about Pivot Charts only tables
Structured range of data a bunch of records/fields that represent some set of
data
We use tables to be more organized, automatic, use names of columns in
references
Sorting, filtering of subtotals
Use pivot tables to categorize things more and analyze at a higher level
Looking at more summarized data
Planning a Structured Range of Data
More common use of worksheet is to manage data (lists of clients, products,
transactions)
Can store/update data, sort, search/retrieve data, summarize, create reports
Structured range of data is a list or table Data definition table: documentation that lists fields to be maintained for
each record and description of info each field will include
Freezing: keeps the headings of a row/column visible while scrolling down
the worksheet
Makes it easier to identify data in each record
Creating an Excel Table
Excel table makes it easier to identify, manage and analyze groups of related
data
Productive and error reducing table features:
o Add new rows/columns to table that automatically expand range
o Add Total row to calculate summary function (ex. SUM, COUNT, MAX)
o Enter formula in one cell that is automatically copied to all other cells
in that table column
o Create formulas that reference cells in a table by using table/column
names instead of cell addresses
Can assign specific name to table making it easier to identify table by content
Table names must start with letter or underscore and cannot include spaces
Check boxes in Table Style Options on Design tab can remove/add elements
and change formatting of table (ex. Display/hide header row)
Maintaining Data in an Excel Table
Use Find command to locate a record and edit data in this field
Sorting Data
Can sort the data in the column you want by using it as the sort field
Ascending order: arranges text from A-Z, smallest to largest, oldest to newest
Descending order: arranges from Z-A, largest to smallest, newest to oldest
Sometimes need to sort more than one column (should use Sort dialog box)
Primary sort field: first sort field
Secondary sort field: second sort field (etc.…)
Can use up to 64 sort fields in a single sort
Custom list: indicates the sequence in which you want the data ordered
Predefined custom sort lists: two days-of-the-week, two months-of-the-year
Use Sort & Filter group on Data tab, click Sort, click Order arrow then Custom
List, enter each entry for custom list, click Add
Filtering Data
Filtering data temporarily hides any records that do not meet specific criteria
In excel table, each column header has a filter arrow
Can filter column of data by: cell colours/font colours; specific text, number,
data; or by selecting exact values you want to filter in column
Can filter using Begins With, Ends With and Contains operations
Ex. “Begins with Sm” would show all records beginning with “Sm” Can further filter data; each additional filter applied to currently filtered data
AND condition requires all of selected criteria be true for record to be
displayed (ex. “Category equals Painting” AND “Condition equals Poor”)
OR condition requires only one of selected criteria be true for record to be
displayed (ex. “Category equals Installation” OR “Category equals Sculpture”)
Criteria filters: enable you to specify various conditions in addition to those
that are base on “equals” criterion
Ex. All art objects valued greater than $20,000 acquired after 7/1/2011
Filtering limits data enabling you to more effectively analyze data
Use Sort dialog box to perform more complex sort
Using the Total Row to Calculate Summary Statistics
Can calculate summary statistics (sum, average, count, max, min) on all
columns in Excel table or on filtered table
Click “Total Row” in Table Style Options group on Table Tools Design tab
Can split worksheet window into two or four separate panes (use Split)
Can easily view data from several areas of worksheet at same time
To create two panes: select cell in row 1 or column A
To create four panes: select any other cell
Inserting Subtotals
Summarize data in a range by inserting subtotals
Subtotal command: inserts subtotal row into the range for each group of data
and adds grand total below last row
Must sort data before using Subtotal command (because inserts subtotals
wherever value in specified field changes)
Subtotal command cannot be used in Excel tables
Subtotal feature also outlines worksheet to control level of detail displayed
Level 3: individual records, subtotals and grand total (most detail)
Level 2: subtotals and grand total
Level 1: only the grand total
Analyzing Data with PivotTables
PivotTables help organize data into meaningful summaries
Must specify which fields in data source you want to summarize
Value fields: fields containing summary data
Category fields: fields that group values in PivotTable (condition, location,
year acquired, etc.)
Category fields used as row labels, column labels and report filters
Can easily hide, rearrange and display different categories in table
Best to create a layout of how you want PivotTable to look before
Great way to summarize data to help support specific points
Can display data as percentages of totals (useful for comparing product sales
with total sales/comparing expense categories to total expenses) Creating a PivotTable
Must specify where to find data for PivotTable (ex. Excel table/range)
Specify whether to place in new or existing worksheet
If in existing worksheet must specify cell that upper-left corner will be in
Place fields from field list (from table) into four areas:
o Report Filter: field used to filter report by selecting one or more items,
enabling you to display subset of data in PivotTable report
o Row Labels: fields you want to display as row (one row displayed for
each unique item, can have nested row fields)
o Column Labels: fields you want to display as columns (one column
displayed for each unique item, can have nested column fields)
o Values: fields you want to summarize
Default: uses SUM function for numbers in Values area, uses COUNT function
for text/nonnumeric values (can change this to whatever function you need)
Can view data in different ways by rearranging fields
Compact report layout: places all fields from row area in single column and
indents item from each field below outer fields
Outline report layout: each field in row area takes column in table (shows
subtotals for each group at the top of every group)
Tabular report layout: displays one column for each field and leaves space for
column headers (total appears at bottom of each group)
Filtering a field lets you focus on subset of items in that field (like in table)
Can sort PivotTable like you sort an Excel table
Can hide field headers and expand/collapse buttons in Show group options
Slicer: provides quicker and easier way to filter PivotTable
Refreshing a PivotTable
Cannot change data directly in PivotTable
Edit data in Excel ta
More
Less
