Excel Tips & Tricks Top 10 tips and tricks to become more

efficient and save time

Leila Gharani, MA Consultant & Trainer

Hyperion Financial Management

Microsoft Excel

Excel Tips & Tricks

1

Table of Contents

TIP #1: BE AWARE OF THE NEW FEATURES ......................................... 2

TIP #2: MASTER THE SIMPLER TASKS.................................................. 4

TIP #3: GO BEYOND THE VLOOKUP .................................................... 6

TIP #4: LEARN NEW FUNCTIONS ........................................................ 8

TIP #5: SPEED UP EXCEL ................................................................... 10

TIP #6: USE CONDITIONAL FORMATTING EFFECTIVELY ..................... 12

TIP #7: EXPAND YOUR HORIZONS .................................................... 13

TIP #8: CREATE A DASHBOARD ........................................................ 14

TIP #9: KNOW THE POSSIBILITIES ..................................................... 16

TIP #10: IMPLEMENT A WORKSHOP ................................................. 18

EXCEL TRAINING & CONSULTING ..................................................... 19

ABOUT LEILA GHARANI ................................................................... 20

Note: Footnotes contain equivalent names of German functions

Excel Tips & Tricks

2

Tip #1: Be Aware of the New

Features

When Microsoft introduced Excel 2007 they introduced new features,

improved functionalities and interface, but many people still use it the

way the used the old Excel. There is much benefit that can be gained

from these new features. They can help you do your analysis faster by

using one of the new formulas or functions introduced. They can help

you create a cleaner and easier to understand file. All in all, they can

help you become more efficient in your work and as a result save time

down the road.

More obvious changes

If you’re currently working with Excel 2007 or above, you’ve probably

noted the more obvious changes. These are:

New user interface

New file format which creates smaller files as compared with

older versions

More rows and columns to build more capable worksheets:

Excel supports over 1 m. rows and 16 k. columns

Memory management increased

Improvements in presentation, reporting and the look of charts

New themes and styles to harmonize reporting books

Easy formula writing using formula auto complete

Better printing experience and ability to save files as PDF

Excel Tips & Tricks

3

Less obvious changes

Some additional features might have gone unnoticed. Below is a

glimpse of the main updates:

New functions: I am quite certain anyone using Excel for any

type of data analysis would highly benefit from these new

formulas. These are SUMIFS, COUNTIFS & AVERAGEIFS1

which allow calculations matching multiple criteria (unlike the old

SUMIF without the “S”) In addition the IFERROR2 formula is

much easier to write than the old If(ISERROR(),,,) formula.

Rich conditional formatting: Excel users employ conditional

formatting to make their data stand out for presentations,

dashboards and analysis. The prior versions of Excel were

restricted to a maximum of three conditions. From Excel 2007

and above, you can use as many rules as required. You can

also apply several rules to a single cell and stop evaluating rules

after a condition has applied. New data formats are also

available such as data bars, colour gradients and icon sets.

Turn your data range into a table (CTRL + T): This feature

was highly improved upon in Excel 2007. To name a few

advantages: it lets you automatically update your charts or

validation lists once you add new data (no need to manually

adjust or use OFFSET formulas). It also lets you use structured

referencing, meaning your formulas update automatically once

you add new data to your rows.

And more improvements: Additional enhancements in terms of

sorting and filtering, shared charting, simpler pivot tables and

easier connections to external data have also been introduced.

1 SUMMEWENNS, ZÄHLENWENNS, MITTELWERTWENNS

2 WENNFEHLER more efficient than ISTFEHLER

Excel Tips & Tricks

4

Tip #2: Master the Simpler Tasks

There are a handful of the more commonly used tools that can help

working with Excel easier. Some might already be obvious to you and

some new. One main advantage of the newer Excel versions is that

some very useful features became easier to access. They are now just a

mouse click away. Previously you had to go through one menu and then

sometimes another, to be able to find the feature you needed.

Customize Excel

In Excel 2007 & above, you can customize the Quick Access Toolbar to

include the buttons and commands you use on a frequent basis. This

toolbar acts as your “favourites” and allows you to group together your

most used Excel buttons. This saves time as you don’t need to switch

between the different ribbon tabs to locate the buttons you need.

From Excel 2010 onwards you can also customize the Ribbon by adding

your own tab and including in that tab all the functions and features you

commonly use.

Use Groups to hide without hiding

Groups are available in the Data tab in the Outline Section. They allow

you to group rows and columns together so that they can be hidden from

view. It is suggested to generally group cells instead of using the hide

cells feature. The advantages are:

Visibility: The + and – signs signal whether there are any

“hidden” rows or columns.

Excel Tips & Tricks

5

Multiple levels: With groupings, you can have multiple levels of

hierarchies which is not possible if you were hiding rows or

columns. The nested groups allow the users to have subtotals

within groups and then drill-downs to a more detailed level.

Excel currently supports up to 8 nested groups.

Navigating large workbooks

When you work with workbooks that contain many tabs you will find

yourself spending a lot of time scrolling left and right to see the one you

need. It makes it easier to right-mouse click on the little scroll bar

arrows at the bottom left-hand corner of your workbook. The right-mouse

click brings up the list of all the tabs in the workbook allowing you to

quickly jump to your selected tab.

And many more

Other more commonly used tools that can make working with Excel

easier are features such as removing of duplicates, introducing

validations, freezing panes, working simultaneously in different windows

or tabs, inspecting workbooks, protecting them, creating templates and

utilizing the potential of custom number formatting. There is always

some useful feature in hiding.

Excel Tips & Tricks

6

Tip #3: Go Beyond the VLOOKUP

The VLOOKUP3 function is one of the most used more “complex” Excel

formulas, because it can search a list or a table in the left-most column

and return the respective value from one of the adjacent columns.

HLOOKUP works exactly in the same way except that it works

horizontally rather than vertically.

Advanced users generally avoid VLOOKUP because of its limitations

VLOOKUP cannot go LEFT: The value that you need to look

up must always be on the first column on the left and the values

to return must be on the right hand side

Two way look up not possible: You cannot VLOOKUP on the

columns and the rows to find an exact match

The INDEX and MATCH4 formula is superior to the VLOOKUP on many

levels

It can look to the left or the right. Unlike VLOOKUP which can

only go right, INDEX and MATCH can look in any direction

You can do a two-way lookup by looking along the row and

along the column in a matrix to find the intersection

At its worst it is slightly faster than VLOOKUP and at its best it is

SIGNIFICANTLY faster than VLOOKUP

Note that INDEX and MATCH is not one function but two functions

written in one formula. Each function alone is not very useful but

together they are very powerful.

The INDEX part returns a value based on the specification of how many

rows to go down and how many columns to go right. The MATCH part

3 SVERWEIS = VLOOKUP, WVERWEIS = HLOOKUP

4 INDEX und VERGLEICH

Excel Tips & Tricks

7

finds the look up value’s row index or column index and passes this

along to the INDEX function. Sounds complicated? It’s actually quite

easy in practice.

All in all if you master the INDEX and MATCH formula be sure that you

will simplify your data analysis and reporting. There are many varieties

and uses of this formula. Below is an example of a more complex matrix

lookup where you need to dynamically move down rows, and also

across the columns. This requires a double match.

Excel Tips & Tricks

8

Tip #4: Learn New Functions

Using the right functions for the task at hand saves time and headaches

down the road. There are many useful functions in Excel that are

extremely practical when doing data analysis.

It’s not so important to know all the formulas by heart. It’s enough that

you are aware of the possibilities and that a certain task could be solved

faster, better, or more efficiently using a given formula or feature.

Below is a list of some very useful functions for data analysis and

manipulation:

SUMIFS, COUNTIFS and AVERAGEIFS functions. These were

introduced with Excel 2007. The addition of the “S” allows for

more criteria to be checked. The traditional SUMIF and

COUNTIF functions allowed only 1 condition whereas the new

functions allow up to 127 criteria. For example you can sum the

values in Column C if the values in Column A and Column B

meet a certain criteria – you can set up to 127 criteria.

Text formulas such as LEN, LEFT, RIGHT, SEARCH, UPPER,

LOWER and PROPER5 are quite useful when you need to

manipulate descriptions, names etc.

Date functions such as WEEKDAY, WORKDAY and

NETWORKDAYS6 can be really helpful when planning your

project deadlines or reporting timetable.

MAX, MIN, LARGE and SMALL7 functions help find the largest,

smallest or the nth largest or smallest value. These are practical

5 LÄNGE, LINKS, RECHTS, SUCHEN, GROSS, KLEIN, GROSS2

6 WOCHENTAG, ARBEITSTAG, NETTOARBEITSTAGE

Excel Tips & Tricks

9

functions for ranking. For example finding the product that

generated the 3rd

highest sales, or the company that had the 5th

highest profit.

OFFSET8 is a very useful function especially when designing

dashboards and flexible reports, as it returns the value or values

in a range by moving from a given starting point.

RANK9 function returns the rank of the number in relation to a

list of numbers. You can use the RANK function together with

INDEX and MATCH or the OFFSET function to dynamically rank

companies based on their results or products according to their

sales value.

SUMPRODUCT10

function is a formula that can work on arrays.

The most common use of it is to get the sum of products in one

formula by skipping intermediary steps. However this function is

much more powerful than that because it can be used in non-

obvious ways. You can actually use this function to do what

INDEX and MATCH can do, what the SUMIFS functions can do

and even what the COUNT function can achieve.

7 MAX, MIN, KGRÖSSTE, KKLEINSTE

8 BEREICH.VERSCHIEBEN

9 RANG

10 SUMMENPRODUKT

Excel Tips & Tricks

10

Tip #5: Speed up Excel

Excel 2007 & above allows you to manage and create bigger and more

complex spreadsheets than what was possible before. It also came with

improvements in terms of speed. It does not however, stop a user from

creating poorly designed spreadsheets by not utilizing the most efficient

function for their calculations which in turn could result in slow-

calculating files and thus frustrated users. By utilizing some

straightforward techniques and understanding how Excel works with

functions, you can significantly improve any slow-calculating workbook.

The design of your Excel spreadsheet is key in defining how efficient

and fast your workbook runs calculations. What makes spreadsheets

slow is not the number of formulas or the size of your workbook but the

number of cell references in your formulas and the efficiency of the

functions used. It is also important to understand how Excel runs

calculations, which cells are calculated and the difference between

volatile and non-volatile functions.

What are volatile and non-volatile functions?

Volatile functions are formulas that automatically get recalculated any

time you enter data in any open workbook. They get calculated

independent of whether your formula is referencing these cells. It could

be anywhere in any cell that has nothing to do with this formula. They

also get calculated anytime you insert a new row or column or you

rename a worksheet or change the position of worksheets.

How do normal formulas work?

Normal non-volatile formulas get recalculated any time you change a

cell that impacts the formula. If you change a cell that has no impact on

Excel Tips & Tricks

11

the formula nothing gets recalculated. Let’s assume you have a large

Excel file that has thousands of formulas and many of these formulas

reference other cells which might in themselves also be formulas

referencing other ranges. This dependency chain is memorized and

stored by Excel in a dependency tree. Excel uses this dependency tree

to decide which cells to recalculate and which cells to ignore. For normal

formula calculation Excel always refers to this dependency chain. If you

make one change to a cell, it updates its dependency tree.

Impact of volatile functions

Note that even though volatile functions get recalculated every time you

make a move in Excel, the use of these functions does not necessarily

slow down your workbook. It just “might” slow it down depending on the

size and complexity of the workbook. If you find that your workbook is

very slow and you are using these functions then it would be best to look

for alternative methods for calculation. Sometimes you can replace

volatile functions with non-volatile ones such as instead of OFFSET, use

INDEX and MATCH function. VBA (Visual Basic for Applications) is also

an option for complicated workbooks and calculations.

Improving your spreadsheet

The major contributors to “slow” files are:

Invalid links to external data & workbooks

Invalid cell references and names

Extensive usage of volatile functions and conditional formatting

Referencing too many unneeded cells, such (D:D) instead of

(D1:D200)

By keeping track of the above points you are well on your way to

creating highly efficient spreadsheets.

Excel Tips & Tricks

12

Tip #6: Use Conditional

Formatting Effectively

Conditional formatting is a very useful tool that helps make your data

stand out. That way information is easier to read and digest. It is

extensively used in dashboards and reports.

You use conditional formatting to emphasize parts of your reports by

applying specific formatting if a certain condition is met. Prior to Excel

2007 a maximum of three conditions were supported. Post 2007 Excel

can have as many conditions as is needed.

You can use in cell bars and icons to bring attention to parts of your data

or highlight a row based on a certain value.

One use of conditional formatting that might have gone unnoticed is its

ability to compare two lists and highlight the duplicate or unique vales.

No need to use functions to compare, just a few simple mouse clicks is

enough.

Bars achieved using Conditional

Formatting in Excel 2010

Excel Tips & Tricks

13

Tip #7: Expand your Horizons

Awareness of functions and features of Excel is crucial in creating

valuable reports and analysis. If you don’t know what possibilities are

available, you will create inefficient reports or incorrect analysis.

Some very useful features are:

Pivot tables offer quick ways of doing analysis. They can do the

same thing as SUMIF, COUNTIF and SUMPRODUCT functions

except that they are significantly faster. In addition, the Pivot

Slicer is available from 2010 which allows for better filtering of

the data, giving your pivot a dashboard look.

From Excel 2010 onwards Microsoft has introduced a free add-

in called Power Pivot which turns Excel into a Business

Intelligence tool. It can be used by general Excel users and

does not require any specific IT knowledge.

Goal Seek and Solver help you solve issues for cases when

you know the end result but are not sure what the input should

be. With Solver you can introduce many constraints as well. For

example you receive a total budget for 10 projects and you want

to distribute it between the projects respecting certain

constraints. Solver can help you do that with a few mouse clicks.

With Name Manager you can create formulas that are easier to

understand and follow. You can also create formulas that can be

used to automatically update charts once new data is added.

Formula auditing features such as the watch window and the

Evaluate Formula tool allow for quick debugging of lengthy

formulas that return errors.

Excel Tips & Tricks

14

Tip #8: Create a Dashboard

Companies that need more complicated systems to handle their

databases and consolidation, still rely heavily on Excel as a data

reporting and analysis tool. Excel is also a great tool for dynamic and

interactive dashboard solutions.

Advantages of dashboards

The main benefits of dashboards are

Visualization: Effective visualization of data is not only

informative but appealing, and as a result more engaging

Highlight problems: Due to the use of visual media you can

highlight trends and changes in KPIs to quickly visualize

problem areas

Avoid complexity: Dashboards allow you to look at different

reports in one place, simplifying data analysis and investigation

Dashboard Example

Tabular reports can make you slow. They consume more energy as you

try to find trends and interpret data.

Excel Tips & Tricks

15

Above is a KPI dashboard created in Excel from retrieving data from

Oracle’s Hyperion system. It enables scrolling to ensure that all

companies are viewable in the same window. It also enables sorting so

that all the companies are sorted based on the KPI chosen. Graphs are

dynamic and animated. The user can for example, tick mark “budget”

and the comparison is provided.

All this is done using PURE Excel formulas. No macros or VBA is

needed to create valuable dashboards. The advantage of such

dashboards is that you can connect the data to any system you have so

that all you have do when you open your Excel dashboard is select your

point of view for month, year etc. and click refresh. The latest

information is instantly available.

Excel Tips & Tricks

16

Tip #9: Know the Possibilities

The problem with Excel is that because it’s so easy to use it’s generally

misused. Anyone can create Excel spreadsheets - badly. To create a

spreadsheet you don’t need special knowledge or be advanced. The

Excel product itself is aimed to work for any type of user independent of

level of knowledge. What can end up happening though is a set of

spaghetti spreadsheets, all somehow intertwined into each other. No

one really understands how they work anymore but somehow they still

work so they continue to use them and pass them on to the next

colleague. Until something goes wrong or something crashes. By that

time no one really understands how to fix the problems anymore.

Sometimes the easiest way of dealing with these spaghetti applications

is to build them from scratch, instead of trying to rework the existing

ones.

Use VBA/Macros to automate

You can use Visual Basic for Applications (VBA or a macro) to create

stand-alone programs and tools that enable you do certain tasks. With

VBA you can:

perform complex analysis

control other applications from Excel

create tools that map data from one database to another

create more sophisticated dashboards

automate any repetitive task

The options are endless. As long as there is a certain logic to a task, it

can be automated.

Excel Tips & Tricks

17

Create valuable templates

Excel can be used creatively and smartly to design templates for

different purposes. The below is an example of an Excel Template that

can be used for project management. It is fully dynamic and uses

formulas, charts and conditional formatting to quickly illustrate how much

of a project has been completed, where the current week is and how

much was originally planned.

Excel Tips & Tricks

18

Tip #10: Implement a Workshop

Reading about new features and functions or attending a seminar that

teaches them is easy. Implementing them in your daily tasks is the real

challenge.

Issue #1: Time

The problem that most people face after attending a technical training is

to actually implement what they learnt in their everyday life. During the

training you might come up with great ideas on how you can improve

your reports, interfaces, create interactive dashboards but once you get

back to your desk you get tied down with your own tasks that you never

find the time to actually implement your new ideas.

Issue #2: How

Sometimes you’re not sure where to start and would like to get some

direction on how best to design your file or tackle the task at hand. A

hands-on workshop will take you step-by-step through the process and

get you started.

In order to support the effectiveness of what you learn and to make sure

the new concepts are also applied in real work life, it’s recommended to

conduct a workshop which allows you to

1. Dedicate the time needed for the task and

2. To get you started in the right direction

This ensures that you not just learn a new method but actually utilize it.

Excel Tips & Tricks

19

Excel Training & Consulting

Once companies are actually aware of what Excel can do in terms of

processing and automating data and reports, it will change how they

work. The Advanced Excel Training session introduces you and your

team to new functions and methods that will help you simplify your work.

Advanced Excel training agenda

Changes that came with Excel 2007 & above

Top 10 tips that can save you time

Top 10 Excel functions every Excel user should know

Go beyond VLOOKUP to learn over 10 valuable functions

The benefits of Excel Tables

Conditional formatting

Name Manager and why it’s useful

Charting tips & tricks

Pivot tables or Goal Seek & Solver

For more information and other available training packages such as the

Excel Basic or the Dashboard training packages please visit

www.xelplus.com

Consulting

You’d like to improve your reporting processes but are not sure where to

start? Or you’d like to introduce automations and checks or map data

from one database to another and are unclear how to approach the

project. Visit the website above for more information.

Excel Tips & Tricks

20

About Leila Gharani

Leila Gharani’s goal is to empower users of Excel

and Consolidation systems with the knowledge they

need to work effectively. Leila supports companies in

solving specific issues in data analysis, reporting and

consolidation. She has over 15 years of experience

implementing and training users on Management

Information Systems of different sizes and nature.

The training sessions are all customized and tailored to the client’s

system and audience. All sessions are hands-on. Especially with

technical topics, users learn the most when they attempt to solve a

problem on their own. They are most engaged when they make

mistakes and attempt to fix these.

Main areas of focus are

Hyperion Financial Management (HFM): advice,

implementation & support

HFM Training: fundamentals, advanced and key user

Excel Information Systems: Automation via VBA, dashboards

& Reports

Excel Training: Basic, Advanced, Dashboards

Education background

Masters degree in Economics, University of Toronto, Canada

Bachelor of Social Science degree, Carleton University, Canada

Microsoft Certified Excel Expert / Office Specialist Master

To find out more please visit www.xelplus.com

Excel Tips & Tricks

21

“Leila has a very deep knowledge of Hyperion Financial Management, both from the

technical and user perspective. As project manager, she was responsible for the

successful implementation of our HFM applications used for consolidation and

reporting purposes of Mondi Group. She is very results oriented, has the ability to

simplify complex subjects, is very analytical, and has a structured working style. Leila

has trained our users with outstanding feedback. Her training sessions are fully

customized based on our needs and requirements. I highly recommend her.”

Walter Seyser, Controlling Director Europe & International, Mondi AG

“The HFM user training showed me how to put together my own Smartview reports

and helped me understand some of the basic and even not so basic functionalities.

The content covered the functionalities that were most practical and helpful for day to

day work, and everything was taught in an easy to understand way. I found the

experience outstanding, much needed and way overdue. Leila is an excellent

instructor. One of the best I’ve ever had.”

Ron Saia, Finance Director, Tekkote, New Jersey, USA

“One thing I liked about Leila’s Advanced Excel training was the practical and

systematic approach. The training included lots of examples and exercises, many of

which were based on our files. Leila’s great knowledge and enthusiasm is a highly

motivating factor for anyone getting trained by her. She can explain complicated issues

in a way that they seem easy and understandable. I have learnt new ways to improve

my Excel skills which I need for my everyday work. I am encouraged to learn more and

improve further. I definitely recommend the training.”

Maja Mahler-Hutter, Europe & International Controlling, Mondi AG, Austria