By

A report by [Link]
[Link]
© 2014
 Happy New Year 2015 to you.

Let me take this opportunity to thank you for all that

you do to me. Thank you so much for each and every
visit, comment, download, purchase, mention of our
site. Thanks to your support, 2014 is our most
successful year since starting [Link].

It gives me immense pleasure to create and share this

report with you. You can learn various aspects of
Advanced Excel by reading this short report and
following the links. Think of this as a starter guide to
your journey to advanced Excel ninjahood.

All the best.

PS: If you think a friend or colleague can benefit from

this report, please forward it to them or send them to
[Link]

Yours,
 Table of contents

Why learn advanced Excel? ............................................................................................................................ 1

1) Formulas ..................................................................................................................................................... 1
2) Conditional formatting ............................................................................................................................... 2
3) Tables & structural referencing .................................................................................................................. 4
4) Pivot tables ................................................................................................................................................. 6
5) Custom charts ............................................................................................................................................. 7
6) Form controls............................................................................................................................................ 10
7) Dynamic / interactive charts ..................................................................................................................... 11
8) Shortcuts & productivity secrets............................................................................................................... 13
9) Creating great looking workbooks ........................................................................................................... 15
Still hungry? More resources for advanced excel ......................................................................................... 16
 WHY LEARN ADVANCED EXCEL?

These days, almost every job requires Excel skills. If you are working as (or aspiring to be) an analyst or manager or
professional (accountant, lawyer, teacher etc.), there is a high chance that your job requires advanced Excel skills. In
this report, learn about top 10 advanced Excel concepts. So if you are ready to become awesome, read on…

1) FORMULAS

Formulas (or functions) can help us calculate what we want. A simple formula like SUM() can be used to calculate
the total of a bunch of values. While formulas like SUM, IF, COUNT, AVERAGE are familiar to anyone, an
advanced user of Excel must know several more formulas. Use below links to start learning advanced formulas:

Formula What it does Links to learn more

SUMIFS Sum up a bunch of numbers that meet a set of conditions. Introduction to SUMIFS
Example: Total sales in north region for the product match
sticks in the month August 2014.

VLOOKUP Looks up a value and returns corresponding data from a table. Introduction to VLOOKUP
Example: Find out the customer’s phone number, given Comprehensive Guide to
customer ID VLOOKUP
VLOOKUP Cheat sheet

INDEX Extract data from a list or table at given co-ordinates. Introduction to INDEX
th formula
Example: Get the value from employees table at the 5 row,
6th column INDEX + MATCH
combination

SUMPRODUCT Calculate the sum of products. Very useful for performing Introduction to
array operations in Excel. SUMPRODUCT formula
Example: Calculate the total sales in north or south region in Advanced SUMPRODUCT
the months October, November & December 2014. Queries

Array formulas Array formulas help us perform same operation on a range (or Example Array formula
array) of values in Excel to get a complex result that is
Formula Forensics (more than
otherwise not possible with regular formulas.
35 array formulas, explained)
Example: Calculate the maximum purchase amount for
customer Steve Steven.

1
 2) CONDITIONAL FORMATTING

Conditional formatting is a great way to highlight values (or data) that meet specific conditions. For example, you can
highlight top 5 customers, bottom 10 employees, all invoices that are overdue etc. using it.

We can mix formulas with conditional formatting to generate really powerful and useful workbooks.

INTRODUCTION TO CONDITIONAL FORMATTING

Conditional formatting is your way of telling excel to format all the cells that meet a criteria in a
certain way. For example you can use conditional formatting to change the font color of all cells with
negative values or change background color of cells with duplicate values.

WHY USE CONDITIONAL FORMATTING?

Of course, you can manually change the formats of cells that meet a criteria. But this a cumbersome and repetitive
process. Especially if you have large set of values or your values change often. That is why we use conditional
formatting. To automatically change formatting when a cell meets certain criteria.

FEW EXAMPLES OF CONDITIONAL FORMATTING

Here are 3 examples of conditional formatting.

SO HOW DO I APPLY CONDITIONAL FORMATTING?

This is very simple. First select the cells you want to format conditionally. Go to Home ribbon and locate the
conditional formatting icon. Click on it and define the type of rule you want.

For example, to highlight all the values between 15 & 25, we can use below rule:

2
 FEW MORE EXAMPLES OF CONDITIONAL FORMATTING

Click on below links to see what powerful & awesome things you can achieve with conditional formatting.
 5 conditional formatting tips to make you a rock star at work
 Highlight duplicates using conditional formatting
 Highlight overdue or upcoming items
 More on conditional formatting

3
 3) TABLES & STRUCTURAL REFERENCING

To be an advanced user of Excel, it is important to know how to structure, maintain, analyze and present your data.
This is where Excel Tables, a new feature introduced in Excel 2007 helps. Tables allow you to maintain all related
data in one place a la Database tables inside an Excel spreadsheet.

With help of structural referencing, you can write plain English formulas in Excel.

With Excel 2013, you can even connect multiple tables using data model feature and create powerful pivot reports.

HOW TO CREATE A TABLE IN EXCEL?

Simple, select any cell in the data you want to convert to table and press CTRL+t

Click ok and your table will be ready.

Once a table is created, make sure you give this table a name from “Design ribbon”. This helps you take advantage of
features like structural referencing with ease.

HOW CAN TABLES HELP YOU?

There are many powerful advantages with tables. Some of them are listed below.

 Create and use structural references. Refer to data as transactions[product], employee[salary] etc, instead of
A2:A1920, B4:B75
 Format with ease. Tables allow you to work with many predefined & gorgeous styles. You can choose one
of them or create your own to instantly transform the formatting of entire table.

4
  Filter, sort and do ad-hoc analysis with ease: When you create a table, Excel adds filtering & sorting
capabilities to tables by default.
 Add calculated columns: You can set up calculated columns that can work the same way for the entire
dataset. If you add more rows later, the calculated columns extend automatically.
 External data as table: when setting up connections to external data sources (like database tables) you can
bring this data as table to Excel so that you can write formulas and create charts faster.

LEARN MORE ABOUT TABLES

Check out below links to learn more about tables.

 Introduction to Excel Tables

 Using formulas with tables
 Structural references in Tables – video

5
 4) PIVOT TABLES

Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze,
explore and present your data.

Excel Pivot Tables help you take a table (or list) of data and then create a report from it, instantly.

For eg. you can take a bunch of sales data and then create a report on region-wise sales performance by Product.

HOW TO CREATE A PIVOT TABLE?

Creating a pivot table in Excel is very simple. Just follow these steps.

1. Arrange your data in a table like fashion. Make sure there are no blank rows.
2. Select your data and goto Insert > Pivot from Ribbon (or press ALT+DP)
3. Set up the pivot table by dragging & dropping columns to rows, columns & values area of the pivot table
4. You are done.

LEARN MORE ABOUT PIVOT TABLES

Click below links to learn more about pivot tables

 Creating pivot tables, an introduction
 Top 5 pivot table tips & tricks
 Grouping data in pivot tables
 Report filters – what are they and how can they help you?
 Slicers – how to use them to create quick dashboards

6
 5) CUSTOM CHARTS

Anyone can create a basic Excel chart. Creating custom charts takes advanced skills. In this section, let’s talk about
few of the common custom charts and how to create them using Excel.

WHAT IS A CUSTOM CHART?

A custom chart is anything that is not found in the Excel’s insert chart dialog.

COMBO CHARTS – 2
DIFFERENT CHARTS MIXED
IN ONE

A combination chart (combo chart) is

a chart that has 2 types mixed in one.
For example a column chart with
lines, as shown aside.

Click here to learn how to create

this chart.

7
 THERMOMETER CHARTS

Thermometer charts are a great way to visualize budget vs. actual data analysis.

Click below links to learn about them:

 Thermometer chart in Excel – a tutorial

 Thermometer chart with last year marker

DONUT BAR CHART

Here is a fun way to explore total sales & product break-up by combining 2 charts in to one.

Click here to learn how to create this chart.

GROUPING RELATED EVENTS WITH COMBO CHARTS

Another example custom chart that combines column chart with line chart to showcase important information.

8
 Click here to learn more about this.

[ADVANCED] ABC INVENTORY ANALYSIS USING CHARTS

Here is an advanced custom chart that shows what is possible with Excel. It depicts ABC inventory analysis based on
inventory data.

Click here to learn more about this chart.

MORE CUSTOM CHARTS

Check out Excel chart examples & templates for more on this.

9
 6) FORM CONTROLS

Form controls help you set up interactivity in your workbook. Let’s say you are building a model in Excel to calculate
cost of a particular project. And your manager asks, “What would be the cost if we hire 30 instead of 20 people?”
Would you go back to drawing board & rebuild your model? That makes no sense.

Instead, you can use features like form controls to provide interactivity to your workbook so that changes like this can
be elegantly handled and you can show instant results.

EXAMPLE – MORTGAGE CALCULATOR

Here is a simple mortgage calculator built with the help of form controls

Click here to learn how to create this in Excel

EXAMPLE – COMPARISON CHART USING FORM CONTROLS

This shows you what happens when you link a form control to a chart.

Click here to learn how to create this chart

WOW, THIS ALL LOOKS TEMPTING, TELL ME MORE ABOUT FORM CONTROLS

We got you covered. Check out introduction to form controls page to learn more about them.

10
 7) DYNAMIC / INTERACTIVE CHARTS

A dynamic or interactive chart helps your users control the chart thru form controls (topic # 6 in this report) or other
features like data validation, slicers or VBA.

EXAMPLE DYNAMIC CHART #1

Here is a simple but very powerful dynamic chart created with combo box form control, picture links & regular
charts.

Click here to learn how to create this.

EXAMPLE INTERACTIVE CHART #2

Here is a very powerful, detailed and interesting chart created with option buttons, regular charts & formulas.

Click here to learn how to create this in Excel.

11
 HIDE / SHOW VALUES IN CHART BASED ON USER DEMAND

This example shows how to deploy check box form controls to interactively hide or show details in chart.

Click here to learn how to create this.

MORE ABOUT INTERACTIVE & DYNAMIC CHARTS

Check out below links to learn more

 Using dynamic chart titles & legends in Excel

 Interactive sales analysis chart
 Top 100 cities to live chart – a case study
 How to make your charts & dashboards interactive – a guide
 More dynamic & interactive charts

12
 8) SHORTCUTS & PRODUCTIVITY SECRETS

It is not enough to know various features of Excel. An advanced user knows how to use Excel productively. This
includes knowing important keyboard shortcuts, mouse shortcuts, work-arounds and Excel customizations.

IMPORTANT SHORTCUTS IN EXCEL

Excel has 100s of shortcuts. Trying to memorize everything is futile. So here is a compressed list of useful shortcuts
that can help you get things done very quickly.

Shortcut What it does?

F2 Opens a cell for editing mode and places cursor at the end

F3 Shows all the names in your workbook for quick access

F4 When writing a formula: switches cell reference styles between absolute, mixed
& relative
Other times: repeats last action

CTRL + T Create a table from selected data

CTRL + Shift + L Turn on / off auto filters

CTRL + arrow keys Navigate workbook data in any direction

ALT + ESV Paste copied data as values

Or Menu button + v

CTRL + K Insert a hyperlink at the selected cell or drawing shape or chart.

CTRL + S Save workbook

F9 Recalculate worksheet formulas

For more: Refer to comprehensive list of Excel keyboard shortcuts

13
 IMPORTANT MOUSE SHORTCUTS IN EXCEL

Just like keyboard, mouse can also boost your productivity when used right.

Here are a bunch of mouse tricks that you must learn & use.

 Using mouse to edit formula ranges: When editing a formula, all the cell
ranges to which the formula refers to are highlighted in different colors. If you
want to include few more rows in your formula, you can just resize the
corresponding colored box. If you want to point to another range in the formula,
just move the colored box by dragging the box border.
 Adjust column widths: by double clicking on the column separator line. This
auto-fits the column width based on cell content.
 Select multiple cells with CTRL+Click: If you need to select a range of cells that are not together (for example
A1, B7, D3, F8, A3), you can hold the CTRL key and click on all these cells. Very useful when writing formulas
or formatting data.
 Auto fill by dragging down: To fill a range of values that follow specific pattern or formula, you can select first
few cells and drag down with mouse.

More on mouse productivity: Using mouse in Excel, Double click tricks

CUSTOMIZING EXCEL TO DO MORE

To boost your productivity often you need to customize how Excel behaves. This includes setting various defaults,
adding custom ribbons & shortcuts and developing personal macros.

An overview of how to customize Excel (podcast)

14
 9) CREATING GREAT LOOKING WORKBOOKS

Advanced users of Excel know how to create great looking, presentable & intuitive workbooks. In this section, let’s
examine some of these techniques.

TIPS FOR CREATING ELEGANT, STYLISH AND BEAUTIFUL WORKBOOKS

1. Use tables to format data quickly. To create a table, just press CTRL+T after selecting any cell in your data.
More about tables in section (3) of this report.
2. Use spreadsheet styles & color themes to change colors across the workbook in
one stroke. You can access this feature from page layout ribbon.
3. Use format painter to copy and paste formatting from one section of your
workbook to other parts. To access format painter, select the source range, go
to Home ribbon and click on the paint brush icon on top left area, then go to
target range and select it.
4. Format for print by using various options in the page layout ribbon
5. When formatting charts, select simple colors and default fonts. This shifts the attention to analysis presented.
6. Make sure you have hidden any unnecessary worksheets, columns and rows using the hide feature available in
home ribbon (Home > format > hide / unhide)
7. Set up freeze panes to show important information always. To freeze first few rows, select the rows, go to View
ribbon and select freeze panes button.
8. Add cell comments or data validation messages to explain things. When you have complex workbook, often
users will have difficulty working with it. You can explain portions of workbook by adding cell comments, data
validation messages at right places. To add cell comments, select the cell and press SHIFT + F2. To add data
validation messages, select cell and go to Data ribbon > validation > Input message and type the message.
9. Create an index sheet: when you have a worksheet with many tabs, make sure you create an index / home sheet
that contains links & explanation about each sheet. Make this the first tab in your workbook so that your users
will not have difficulty working with it.

For more about creating great looking workbooks, reports & models, refer to below pages:
 Tips for creating better worksheets
 8 Excel formatting tips to make you a pro
 Chart formatting tips that won’t cost you $150,000
 Best practice modeling – make these 5 changes today

15
 STILL HUNGRY? MORE RESOURCES FOR ADVANCED EXCEL

If you want more information, resources and articles about Advanced Excel, check out below pages.

 Advanced Excel skills – what are they and how to learn them?
 Writing advanced formulas – 35+ examples
 Advanced Excel articles @ [Link]
 Advanced Charting articles @ [Link]

All the best & happy holidays.

16