JULY 2017
6
Excel tricks for increased efficiency
By Andrew Howard
Considering the time business professionals spend staring at Microsoft Excel spreadsheets,
one might think we'd all be Excel wizards by now. The truth is that while Excel has a low
barrier to entry, to become a true super user takes a lot of time, energy, and dedication.
While you may think you're an above-average user, adopting a few quick Excel tricks can
exponentially increase your efficiency — and save you tons of time in the long run.
Andrew Howard
Andrew Howard makes his living at the crossroads of
education and technology. He is the author and instructor
of Excel Tips and Tricks, an in-house Excel training course
for Learning Tree International employees. He has worked
in education for six years, the last two as a data and sales
analyst for Learning Tree.
Learning Tree offers practical, real-world training in
today's most in-demand skills, including Microsoft
Office, SharePoint, project management, leadership and
professional development.
Visit
www.LearningTree.ca for more information.
1. Adopt some useful keyboard shortcuts
Here are a few easy and effective shortcuts you should
add to your arsenal today:
Shortcut Feature
CTRL + Arrow
Keys
Quickly move around within your
data. For example, CTRL + Down
Arrow selects the last cell below the
current cell that has data in it before
it hits a blank cell.
F2
Begin editing the selected cell (just
like double clicking the cell)
F4
Repeat the last action you took (e.g.,
applying formatting or deleting a row)
F12
The same as selecting Save As…
from the File tab, but much quicker.
When Editing Formulas
F4
F4 will toggle the leading $ which
makes the column and row parts of
your references static (A1, $A$1,
$A1, A$1)
F9
Also, while editing formulas,
pressing F9 will evaluate the
selected part of your formula.
This can be useful when error-
checking your formulas.
TAB
Autocomplete the name of the
function that matches what
you've typed. For example, if you
type "=CON" then press TAB,
the formula will autocomplete to
"=CONCATENATE(".
Alt + Enter
Insert a line break into a cell without
leaving formula editing mode.
3. Add commonly-used features to the Quick
Access Toolbar (QAT)
4. View duplicate cells with Conditional
Formatting
You may know you can remove
duplicate rows with the
Remove Duplicates feature on
the Data tab, but what if you
just want to see them without
removing them? Try creating
a new Conditional Formatting
rule to highlight duplicate cells
(or, alternatively, unique cells).
2. Use the "New Window" button on the
"View" tab
You'll be able to see different sheets from the same file
in separate windows, minimizing the need to click back
and forth between sheets.
Taking advantage of the QAT eliminates the need to
search the ribbon for your most-used features. To add
a feature to the QAT, right-click the item in the ribbon
and click "Add to Quick Access Toolbar."
