Home > Jan's CompLit 101 > Working with Numbers > Formulas > Summary

Jan's Working with Numbers

Formulas: Summary

Grouping similar data lets you create subtotals for each group. The data must be sorted first. You can sort on more than one column.

Another way to get subtotals is with a PivotTable which summarizes data based on two characteristics and shows a 'total', like SUM, MAX, MIN, COUNT.

Moving or inserting cells can scramble formulas. You can often repair the broken formulas with AutoFill or by editing the cell references to include the new cells.

What You Printed for Excel Project 3:

	File and sheet	What is it?	Which lesson?	# of pages
1.	trips22-Lastname-Firstname.xlsx - Agents Totals	Subtotals with just subtotals displayed	Subtotal	1 page
2.	trips23-Lastname-Firstname.xlsx - Formatted Groups	Gray scale	Format Groups	1 page
	trips23-Lastname-Firstname.xlsx - Formatted Groups	With alternate formatting for groups	Format Groups	1 page
2.	trips23-pivottable-Lastname-Firstname.xlsx - PivotTable	Sheet showing a PivotTable and a PivotChart for agents and trips	Pivot Table	1 page
3.	trips23-pivottable-Lastname-Firstname.xlsx - PivotTable	Sheet with slicer lists showing	Pivot Table	1 page
4.	trips27-Lastname-Firstname.xlsx - Specials	Top table with merged cells for 'Cost each' and fixed formulas for 'Total sale'	Merges & Formulas	1 page
5.	trips27-Lastname-Firstname.xlsx - Formatted Groups, Tahiti, New Zealand, World	Repaired linked cells after changes to Specials	Merges & Formulas	5 pages
6.	trips29-Lastname-Firstname.xlsx - Specials	Print selection of upper table with inserted rows and repaired formulas	Inserting & Formulas	1 page

Skills Covered - in lesson

Sort

Copy a whole sheet
Move a column
Use Sort dialog - multiple columns and a custom list

Subtotal

Use Subtotal command
Expand/collapse subtotal display
Edit borders with Format Cells > Borders tab

Format Groups

Format data group - background, border, merge duplicate data
Move a table
Print in black and white to check formatting
Revise colors based on how they print

Pivot Table and Chart

Create a pivot table
Filter a pivot table
Change the function for the pivot table
Create a pivot chart
Use slicers to filter pivot table/chart

AutoShape

Show/hide AutoCalculate functions on the Status Bar
Use AutoCalculate
Insert an AutoShape
Format an AutoShape
Copy and paste an AutoShape
Delete an AutoShape
That shapes are attached to cells but are not in the cells

Image

Delete a sheet
Delete data from a column
Insert an image from a file
Move and resize image
Search for and insert an online image
Select cells underneath an image
Modify grouped sheets

Copies & Formulas

Find out if a cell is a copy or if it is linked
Change a copy to a link for labels and data cells
Deal with merged cells when editing

Merges & Formulas

Merge cells
Repair formulas with absolute reference
Repair formulas with AutoFill
Repair formulas on sheet with linked cells

Sorting & Formulas

How sorting fails with merged cells
Write formulas using values from two rows
Create false calculations by sorting rows with the new formulas

Inserting & Formulas

Insert row without breaking formula - inside range, at bottom of range, below bottom of range
Insert row that breaks formula - at top edge of range
Fix a formula broken when cells were inserted
Check links on sheets affected by inserting new rows