If you like this presentation – show it...

Slide 0

5
OFFICE 2016. EXPLAINED.
Quick Tips!
Excel Tips You’ll
Actually Use
These 5 little-known tips for Excel will help you troubleshoot complex
worksheets, document complex formulas, zoom in on your spreadsheets, copy
visible data only and learn a new way to organize your pivot tables.
GET STARTED

Slide 1

TIP 1
Tracking down cell dependencies
When you have a complex worksheet, where the value in one cell
impacts several other cells and formulas, press Ctrl-Shift-] (right bracket)
to see the dependencies.
NEXT SLIDE

Slide 2

Quick tip!
You can even click on the dotted lines and
see if the cell refers to a different worksheet
or workbook.
With a cell selected, click
on Trace Dependencies
from the Formula ribbon
and you’ll see lines from
the cell to the dependent
cells. Click Trace
Dependencies a second
time and you’ll see the
dependencies of those cells.

Slide 3

TIP 2
Document
formulas
Use FORMULATEXT and the N function to quickly document the formulas in
your worksheet. In the cell next to your formula (or any cell for that matter),
use the function =FORMULATEXT(cell number goes here). This will display
your formula making it easy to know how you calculated that value.
NEXT SLIDE

Slide 4

Quick tip!
TIP 2
Document formulas using +N
If you don’t want to display the formula on the worksheet, at the end of your
formula add +N(“your formula description goes here”)
The resulting value of the formula won’t change, but you have now
described the formula for future reference!
NEXT SLIDE

Slide 5

TIP 3
Quick zoom
Ctrl
+
When you want to quickly zoom in on a section of your worksheet, skip the
Zoom buttons on the View Ribbon. Instead, hold down the Ctrl key on your
keyboard and use the Scroll Wheel on your mouse to zoom in and out of
your worksheet!
NEXT SLIDE

Slide 6

TIP 4
alt
+
;
Copying visible data from a table
with hidden rows/columns
When you only want to copy the visible data from a table that has hidden
rows and or columns, use this little known keyboard shortcut before you
press Ctrl-C to copy: Alt-; (alt-semi colon)
When you press Alt-; ﬁrst, it will tell Excel to only copy the visible data and
exclude any data or values in hidden rows or columns.
NEXT SLIDE

Slide 7

TIP 5
Date & time
as a pivot
table metric
Using date and time as a metric is one of the most powerful ways to analyze
data in a pivot table–except when your data is listed by day instead of Year,
Month or Quarter. Here’s how to convert your dates.
NEXT SLIDE

Slide 8

Before
In your pivot table, select
one of the cells that
contains a date and then
Right Click and choose
Group.

Slide 9

After
This will group your data so
you can analyze it better.

Slide 10

FOR MORE TIPS ON GETTING THE MOST OUT OF EXCEL, VISIT
LYNDA.COM/EXCELTIPS

Slide 11