5
Administrative Assistant's
UPDATE
Excel tricks for increased efficiency: Part 2
Editor's note: In the July issue of AAU we published Part I of Excel tricks, including (1) key-
board shortcuts, (2) using the New Window button on the View tab, (3) adding commonly
used features to the Quick Access Toolbar, and (4) viewing duplicate cells with Conditional
Formatting.
By Andrew Howard
5. Leverage Named Ranges
Named Ranges make it easier
to refer to single cells or a
range of cells in a formula by
giving them a name. Simply
select a cell or range of cells,
click the "Name" box in the
Formula tab and enter a name.
Now, when you need to refer
to the Named Range in a for
-
mula, just start typing its name
and Excel will know which
cell(s) you are referring to.
6. Create drop-down lists with Data Validation
Data validation ensures that correct data is being en-
tered in a spreadsheet. To create a drop-down list, select
the cells in which you want to add a drop-down menu,
then choose Data
Validation on the
Data tab. Choose
List and type your
list items in the
Source field. Com
-
bine with Named
Ranges to create
dynamic drop-
down lists that
pull from a range
in your spreadsheet.
7. Employ Tables and Structured References
Using Tables and Structured References together makes it
easy to group like data and can simplify formula writ-
ing. It also makes your data more elegant and easier to
read with easy formatting templates. Managing data is
simpler as well, as you can insert and delete rows and
columns without interfering with other data on the same
sheet.
8. Use the Group feature to quickly show and
hide columns or rows
You don't always
need to see all the
data in your spread-
sheet. Use the Group
feature to quickly
show and hide col-
umns and rows you
don't always need to
see. Just select the
section you want to
treat as a group and
then select the Group function on the Data tab.
9. Take your skills to the next level with
PowerQuery and PowerPivot
So, you already
know the tricks
above and more.
That's great, and
there's still plenty
of powerful features
you can learn!
PowerQuery is a powerful tool that allows you to pull
data into Excel from many different sources. PowerPiv
-
ot allows you to use Excel more like a relational data-
base, linking multiple datasets into a single data model
through key-based relationships.
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.
