ABSTRACT
This seminar is designed to showcase little
known Excel tips and tricks, with the goal of
helping attendees work more quickly and
accurately in Microsoft Excel®.
The seminar will start out with generic tips
that appeal to everyone who generates
spreadsheets and will cover intermediate and
advanced tips and tricks as the seminar
progresses.
This interactive seminar is taught in plain
English – not geek-speak, and is designed to
EXCEL TIPS, TRICKS, help you work smarter, rather than harder, in
Microsoft Excel®.
AND SHORTCUTS Dr. Holly A. Sullenger, PhD
©2022
Working Smarter – not Harder!
Dr. Holly Speaks, PhD, LLC
Dr. Holly A. Sullenger, PhD
www.DrHollySpeaks.com
[email protected] www.Facebook.com/DrHollySpeaks
www.LinkedIn.com/Company/DrHollySpeaks
1-919-649-8068
7 Steps to Spreadsheet Success
Setting up a spreadsheet can be tedious. Fortunately, there is a simple 7-step method for creating a
spreadsheet.
The Seven-Step Method is:
1. Enter a Title
2. Enter Row Labels
3. Enter Column Headings
4. Enter the Data and Save the Worksheet
5. Perform any Calculations (and save again!)
6. Enhance/format the Worksheet (and save again!)
7. Print the Worksheet (and save again!)
A spreadsheet
put together using
the 7-Step Method
Tips and shortcuts every user should know Notes
The amazing “fill handle”
Copy by drag
Keyboard shortcuts for date, time and summing
Pre-selecting a range for quick data entry
Multiple selection AutoSum features to access
business statistical math functions
The highly-guarded secret for creating charts
effortlessly
©2022 Dr. Holly Speaks, PhD, LLC Excel Tips, Tricks, and Shortcuts Page 1 of 6
The Magic Chart Key
Numeric data is best presented to humans in picture form rather than in raw form. In other words, most
people would prefer to see a chart rather than actual numbers.
Fortunately, charts are very easy to produce in an Excel worksheet environment. Charts can be created to live
alongside the data in Excel, or to exist on their own sheet. Different audiences will be interested in one or the
other, so it is best to learn both!
To create a separate chart sheet in an Excel worksheet, follow the steps below:
1. Highlight the data that should be graphed
2. Press the F11 function key (the “Magic” Key!)
Chart Created with the “Magic” Key
Tip: To highlight contiguous cells, use the “Shift key” highlighting method. To
highlight non-contiguous cells, use the “Ctrl key” highlighting method.
©2022 Dr. Holly Speaks, PhD, LLC Excel Tips, Tricks, and Shortcuts Page 2 of 6
Bringing Order to Chaos with Sorting
The Sort tools are found on the Home and/or Data tabs on the Ribbon. The Sort options are also
found by right clicking on any piece of data within an Excel list.
Sort allows the data to be rearranged/ordered by a specific field or fields.
TIP: You do NOT have to highlight to sort if your spreadsheet has
been properly constructed.
To sort on one column, use the A to Z sort buttons on the Home or Data tabs on the ribbon, or right
click and select Sort.
To sort on multiple columns, use the Sort tool and the sort dialog window.
This same window can also be
accessed with a right click, selecting
Sort, and selecting Custom Sort.
©2022 Dr. Holly Speaks, PhD, LLC Excel Tips, Tricks, and Shortcuts Page 3 of 6
Analytics via the Subtotals Tool
The Subtotals tool
places subtotals and
grand totals
throughout the
spreadsheet at
specified grouping
intervals.
And, the Subtotals tool
allows you to select
from various business
statistical math
functions.
TIP: For this to work,
sort the list into the desired order first,
then invoke the Subtotals tool.
The Subtotals tool will supply a
left margin “outline” that allows
the user to control which groups
are displayed and which groups
are hidden, and the level of detail
to which data is shown.
©2022 Dr. Holly Speaks, PhD, LLC Excel Tips, Tricks, and Shortcuts Page 4 of 6
Fun with PivotTables
PivotTables bring order to big data. They allow us to summarize, analyze, and play – all with the end-goal of
making efficient and fruitful business decisions.
Use the Insert tab on the ribbon, and the
PivotTable tool.
If your list is set up properly, Excel will know
what data to include.
Click on OK on the Create PivotTable
window.
Use the sandbox on the
right side of the window to
create the PivotTable.
Use the Analyze tab on the ribbon to insert a PivotChart – and use the Analyze tab on the ribbon to insert
Slicers – a much easier way to “drill down” to the data that will be most helpful.
PivotTables are an excellent example of data mining!”
©2022 Dr. Holly Speaks, PhD, LLC Excel Tips, Tricks, and Shortcuts Page 5 of 6
Wrap Up
Congratulations on completing Excel Tips & Tricks!
You now have a new set of analytics tools on your technology tool belt.
Please remember that as humans, we don’t remember much unless we put it to practice and use it on a
regular basis. So, practice when you return to work! Train someone in the office on the new concepts you
think would be most useful.
If you have any questions about the material contained in this seminar, please contact Dr. Holly using any of
the contact points listed below.
Happy Excel-ing!
Dr. Holly Speaks, PhD, LLC
Dr. Holly A. Sullenger, PhD
www.DrHollySpeaks.com
[email protected] www.Facebook.com/DrHollySpeaks
www.LinkedIn.com/Company/DrHollySpeaks
1-919-649-8068
©2022 Dr. Holly Speaks, PhD, LLC Excel Tips, Tricks, and Shortcuts Page 6 of 6
