Today, lets understand how to use these formulas better.

What is the syntax for Match, Vlookup and INDEX?

Here is the syntax for these three very powerful functions in plain English:

What are vlookup () and match () ?

VLOOKUP and MATCH are your way of asking excel to find a needle in haystack. Imagine you have all your customer contact information in one sheet in the range A1:D5000 in the format phone number, name, city and date of birth. Now you need to find out which customer has the phone number “936-174-5910”. How do you do it?

You guessed it right, you use VLOOKUP and summon excel to do the search and return with customer name.

While VLOOKUP is used to fetch value a based on what you are looking for, MATCH is used to fetch the position of the value you are looking for.

See this illustration to understand :

What does VLOOKUP really do?

Imagine you have a list of data like this:

Now, how do you answer the question – “How many sales did Jimmy make?“

Yes, your guess is right. VLOOKUP is one of the formulas you can use to answer questions like this.

VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns.

So, in our case, we need VLOOKUP to search for Jimmy and return the amount of sales he made from column 3.

VLOOKUP Syntax & Examples:

The syntax of VLOOKUP is simple:

=VLOOKUP( this value, your data table, column number, optional is your table sorted?)

Here is an example to get you started:

Learn more about VLOOKUP Formula with examples

Please check out this page for 10+ examples of VLOOKUP and how to use it to solve real world problems.

VLOOKUP Examples & Homework

I have made a small excel file detailing 4 VLOOKUP formula examples. The file also contains some home work so that you can practice this formula.

Download VLOOKUP Example Workbook

[NEW] XLOOKUP replaces VLOOKUP in Excel 365

If you are using Excel 365, you can use the new & improved XLOOKUP function. It offers a shorter & more versatile syntax for performing lookups.

For ex: the same lookup as above will be done with XLOOKUP like below:

=XLOOKUP(“Jimmy”, A2:A14, C2:C14) will lookup “Jimmy” in column A and return sales amount from Column C.

Click here to learn more about XLOOKUP.

So what is INDEX() then?

INDEX function is your way of telling excel to fetch a value from large range of values. Since MATCH() function can tell us where the data is found, you can then use INDEX() function to extract corresponding data from another column. In this case, we can use MATCH() to find out which row has net sales 1,799 and INDEX() to return the name of the person. Like this:

Find the position of 1,799 in sales: =MATCH(1799, $C$2:$C$14, 0)

The answer will be 8.

To find the 8th person in names list, we can use INDEX() function like this:

=INDEX($A$2:$A$14, 8)

The answer will be Jagjit.

Related: Learn more about INDEX Formula.

So how are INDEX() and MATCH() linked to each other?

Since MATCH returns the position of the item you are looking for in a list, you can then use this position in INDEX to fetch values surrounding the searched value.

So, we can combine both functions like this:

=INDEX($A$2:$A$14, MATCH(1799, $C$2:$C$14, 0))

This combination is called as INDEX+MATCH formulas.

Related: Using INDEX + MATCH functions & INDEX+MATCH Video

Finally

Remember, both VLOOKUP and MATCH throw a fail error of #N/A if the value you are looking for is not there. If you want to stop seeing the error, use IFERROR function.

Just use them with some dummy data, play around with arguments and see how you can say “oh yeah, I can do that in few minutes” to your boss next time.

VLOOKUP tutorial – video

Please watch this quick video tutorial to understand all these concepts and how to write VLOOKUP formulas easily.

INDEX MATCH Tutorial – Video

Want to Learn More Formulas? Get my VLOOKUP book

If you want to learn VLOOKUP and other Excel lookup functions, then consider getting my VLOOKUP book.

Comprehensive and easy to understand This is a book for everyone who uses Vlookup. Most of us think… Oh.. I already know the function. But this book will open your eyes to some brilliant techniques. – By Dr. Nitin Paranjape Solid introduction to lookup functions This books does a wonderful job of taking each of the lookup functions available in Excel, breaking them down to a simple, easy-to-understand level. – by Lucas Moraga Get your copy

Read next…

• VLOOKUP formula with 10+ examples
• XLOOKUP – an improved version of VLOOKUP
• How to use INDEX formula
• How to use IFERROR to stop errors with VLOOKUP

The post VLOOKUP(), MATCH() and INDEX() – explained in plain English appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Excel pivot tables are very useful and powerful feature of MS Excel. They are be used to create instant summaries, reports and data analysis from your raw data. 

In this page, learn all about how to create an Excel pivot table and customize it.

Table of Contents

What are Excel Pivot Tables?

A pivot table turns your data into report format. Here is a sample Pivot table from sales data, showing total sales by region.

How to create a Pivot Table?

We will use 2019 sales data of a fictional company. This data contains 466 rows of sales information in columns – Month, Salesman, Region, Product, No.  Customers, Net Sales, Profit / Loss. Here is a preview of our data.

Download the sample data & example pivot tables here.

To create a pivot table showing totals sales by region, follow these steps.

1. Select any cell in the data.
2. Go to Insert ribbon and click “Pivot Table” button.
   
3.  Click ok on the next screen.
   
4. You will be taken a new spreadsheet with blank Pivot Table canvas.  Here, using the Pivot Table Fields panel set “Regions” field to row label area, “Products” to “Filter” area and “Net Sales” to values area. See below illustration.
   

Your pivot table will be ready. We can see that “West” is our best region. This is why Pivot tables are easy for finding answers to common business questions.

Two dimensional Pivots - Row & Column fields

You can add fields to both “Row” and “Column” label area of a pivot. Such Pivot Tables are normally called two dimensional pivots. Here is a demo of a two dimensional pivot table showing Total Sales by Region & Sales Person.

Multi-dimensional Pivots - Row & Column fields

You can also add more than one item to “Row” or “Column” label area. This creates a multi-dimensional Pivot Report. Here is one such pivot report showing total sales by Region, Sales Person & Product for selected months.

 

How to format Pivot Table values?

By default, numbers in Pivot Tables tend to just look like zip codes, without any proper formatting. This is easy to fix though. Simply right click on the values and use “Value Field Settings” to set up the formatting. To set currency formatting for our Total sales by region Pivot Report, 

1. Go to value field settings
2. Click on Number Format button
3. Set up the formatting to “Currency”
4. Done.

See this illustration.

 

Sorting in Excel Pivot Tables

You can easily sort pivot report by ascending or descending order of the value. To do this, just right click on the value, select Sort > and specify the order.

Here is an example of sorted pivot report of Number of customers by Sales person.

Bonus tip: Manual sorting You can also manually adjust the order of Pivot items by dragging and dropping them. See this tip in action.

Filtering Excel Pivot Tables

You are looking at Regional total sales and want to know what the total is for just “RapidZoo” product. You can do this by filtering the pivot table. Excel offers two powerful ways to filter Pivot Tables

1. Report filters
2. Slicers

Both methods are illustrated below. Read on to learn how to use them.

Filtering with Report Filters

Report filter is a great way to restrict the data that is flowing to your pivot. To set them up, just add the field to “Filters” area in the fields panel. Now, using the filter button next to “Product”, select the product you want.

Here is a quick demo of report filters in action.

Filtering with Slicers

There are a ton of cool features in Excel Pivot Tables, but slicers are hands-down the best feature. At-least, that is what I think. They make filtering and ad-hoc data analysis a breeze.

A slicer is a visual filter. You can add a slicer on any field by right clicking on it from the fields panel. See the illustration “Adding filters to a pivot report” from above. 

Once you have a slicer on Product, simply click on any product name to see the report for that. 

Here is a quick demo of Pivot Table with slicers.

Bonus Tip: Use Timeslines for date filtering When you have a date field (Month in our data), you can use special type of a slicer, called timeline. This enables you to interactively select a portion of time.

Other kinds of filtering - Value & Label Filters

Apart from report filters & Slicers, Pivot Tables also allow you to filter by a field or value. 

Field or Label Filter: If you don’t want to see “Middle” region in a row label area, just click on the filter button next to “Row Labels” and uncheck the region. This type of filtering is called Label Filtering.

Value Filter: If you want to see just the top 2 regions by total sales, then you need a value filter. Simply go to filter button next to row labels and using value filters, apply a top 10 filter but set it to top 2 values by “Sum of net sales.”

Changing Calculations in Pivot Tables

The default calculation in Pivot Tables is SUM for number fields and COUNT for all others. But you can also customize the calculation easily. Just right click on the value field and choose different type of summary from right click menu.

Changing from SUM to AVERAGE in a Pivot Table

Here is a quick illustration of how to change calculation type from “SUM” to “AVERAGE”.

Pivot Table Layouts & Colors

By default, Excel Pivot Tables are in compact layout. This means, if you add multiple fields to row label area, they will all be shown in same column, with indentation.

You can change the layout of a pivot table to other formats too. 

• Compact form (default)
• Outline form
• Tabular form

You can change the layout from Pivot Table Design ribbon.

Here is an example of same Pivot Table in both Compact and Tabular layouts. 

Styling & colors of Excel Pivot Tables

You can apply any formatting to the pivot tables. MS Excel has some very good pivot table styles. Just select pivot table cells, go to Pivot Table Design ribbon. See below image to understand various options available.

Bonus tip: Copy Pivot Tables to format quicker If you like the formatting (colors, layout etc.) of a Pivot Table and want to use the same set up for a new Pivot Table, simply copy and paste the original table. Then you can change the fields and your new Pivot will be ready.

Visualizing with Pivot Charts

You can use Pivot Charts to visualize the same information in a graphic format. Here is a sample Pivot Chart of Net Sales by Region & Product.

Steps for creating a Pivot Chart:

1. Select any cell in the Pivot Table.
2. Click on Insert > Chart or Analyze > Pivot Chart button.
3. Insert the type of chart you want.
4. You will get a Pivot Chart.

Bonus tip: Add Pivot Chart directly from data You can insert Pivot Chart directly from data. Just select your data and press Pivot Chart button in the insert ribbon (in charts area). Use Fields Panel to set up the chart.

Interactive Pivot Chart with Slicers

Slicers make it incredibly easy to create interactive charts. Once you have a regular Pivot Chart, simply add a slicer to it (right click on the field in “Pivot Table Fields” area and select “Add as Slicer”). You now have an interactive Pivot Chart.

Here is a demo of interactive Pivot Chart. 

Updating Pivot Tables (Refresh)

Whenever you have new data, just use “Refresh” button to update your Pivot Tables. You can find this button in multiple places.

• Data ribbon
• Pivot Table Analyze ribbon
• On right clicking any Pivot Table
• By pressing ALT+F5 (refreshes single pivot) or CTRL+ALT+F5 (refreshes all pivots)

What if you want to point Pivot to new data?

Select any cell in the Pivot Table and from Analyze ribbon, use the “Change Data Source” button. Point input data to a new source. As long as the new data has same fields, everything will work smoothly.

 

 

Pivot Tables in Excel - Complete video tutorial

I have made a 21 minute video explaining how to create, format, customize, visualize, filter and refresh Pivot Tables. This video is packed with many tricks, ideas and inspiration. Check it out below.

Download - Sample data & example Pivot Tables

Please click here to download the sample file for this article. It contains fictional sales data, several example pivot tables, charts and additional resources. 

Examine the pivot table settings and use the data to learn more.

Next Steps

Now that you are familiar with Pivot Tables, explore these additional pages to learn more about data analysis & reporting.

Beginner:

• Pivot Table from multiple tables – Data Model & Relationships
• Number and Percentages in same Pivot
• 5 Pivot tables to try when you have too much data
• Sub-totals but only on one level
• Distinct count in Excel Pivot Tables
• How to use Report Filters

Intermediate & Advanced Users:

• All you need to know about Slicers
• 6 Time Saving Pivot Table Tricks
• Advanced Pivot Table Tricks
• Conditional formatting for Pivot Tables
• Introduction to GETPIVOTDATA
• Getting started with Power Pivot – Percentage of something calculation example

Recommended Websites & Books:

These are my favorite places to learn more about Pivot Tables.

Websites:

• Excel Pivot Tables page on Contextures
• Pivot Table tips from Excel Jet
• 50 things you can do with Pivot Tables from MyExcelOnline

Books:

• Excel Pivot Table data crunching by Bill Jelen
• MS Excel Data Analysis and Business Modeling by Wayne Winston
• Excel Bible by J Walkenbach

Courses:

• Excel School program by Chandoo

Happy Learning.

 

The post Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

What is an Excel table?

Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”

When you make a table (more on this in a sec) you can easily add more rows to it without worrying about updating formula references, formatting options, filter settings etc. Excel will take care of everything thus making you a data guru.

How to create table from a bunch of data?

To create an excel table, all you have to do is select a range of cells and press the table button from Insert ribbon in Excel (or use the shortcut CTRL+T).

See this simple tutorial:

Today we will learn 10 excel data table tricks that will make you a data guru, no let’s make that DATA GURU.

The most important thing after you create a table – Give it a name

Once you have a table, go to design ribbon and give your table a name. If you don’t name it, Excel will call it Table2 or whatever. But once you name it, you can write meaningful formulas thru sweet sweet structural references feature. So name your tables.

1. Change table formatting without lifting a finger

Excel has some great predefined table formatting options. Just select any cell in your table and change the table formatting by going to “format as table” button in the home ribbon.

If you are bored with the predefined formats, you can easily define your own table formatting color schemes and apply them.

2. Add Zebra Lines to Tables without doing Donkey Work

When you create a table, zebra lines come as a bonus. And when you add new rows to the table, excel takes care of zebra lining or banding automatically. You can turn on / off the banded rows feature from “design ribbon tab” as well.

That means you don’t need to use conditional formatting or manually format alternative rows in different color.

3. Tables come with Data Filters and Sort Options by default

Each data table comes with filters and sorting options so that you can filter and sort the data in that table independently. That also means, if a worksheet has 2 tables, they each get their own data filters (usually excel wont allow you to add more than one set of filters per sheet, but when it comes to tables, all exceptions are made, just for you)

4. You can also Slice your tables with slicers

That is right. When you have a table of data, you can insert a slicer (either from design ribbon or insert ribbon) and use that to filter your table data intuitively.

Learn all about Excel Slicers.

5. Bye, bye cell references, welcome structured references

The most important advantage of tables is that, you can write meaningful looking formulas instead of using cell references. When you create and name the table (you can name the table from design tab), you can write formulas that look like this:

The beauty of structured references is that, when you add or remove rows, you don’t need to worry about updating the references.

Learn all about structural references in Excel.

6. Make Calculated Columns with ease

Any tabular data will have its share of calculated columns. Excel tables make having calculated columns very easy. With structured references, all you need to know is English to make a calculated column. The beauty of calculated columns in table is that, when you write formula in one cell, excel automatically fills the formula in the rest of cells in that column. That would make you an instant data guru.

7. Total your Tables without writing one formula

The ability to summarize data with pivot tables is extended to excel tables as well. You can add total row to your table with just a click.

What more, you can easily change the summary type from “sum” to say “average”.

8. Convert table back to a range, if you ever need to

If you ever wanted to go back to a normal range of data, you can easily convert the tables back to named ranges.

Excel will take care of the formulas and change the references to cell references.

9. Export Tables to Pivot Tables, Woohoo

What good is a bunch of data when you can’t analyze it? That is where Pivot tables come in to picture [pivot table tutorial]. Thankfully, you don’t need to do much. Just click a button and your table goes to pivot table.

10. Push the table data to Sharepoint Intranet Site

If you have a corporate intranet Sharepoint portal, you can easily publish the excel tables as share-point lists. This can be handy if you want to publish, say the top 10 sales persons of the quarter on the intranet.

11. Print Tables Alone, with out all the other stuff around

Select the table, hit CTRL+P and in settings area, select “Print Selected Table” option to print your beautifully formatted Excel table.

12. Change, reshape or clean your table data with Power Query

When you have data in a table, you can easily load it to Power Query (Get & Transform Data) using the “From Table” button.

Here is an an example of what Power Query can do for you.

13. Got multiple tables? Connect them to make a multi-table pivot

When you have more than one table, you can also connect them using Excel’s relationship feature. This way, you can build multi-table pivots to create powerful analysis of your data.

Learn all about Excel Table Relationships.

So, What do you think about Excel tables?

I say, give them a try. They have been around for more than a decade, but I still see people not using them. Setting up your data as a table is the easiest and most awesome thing you can do it. You can find some cool uses for tables in your day to day work. They are intuitive, easy to use and provide great power without added complexity.

Related Material

• Beginner:
  • Using Structural References with Tables
  • Connecting multiple tables
  • Writing lookups or other formulas on tables
• Advanced:
  • Relative referencing in Tables
  • Sum & Count filtered values in Tables
  • Custom table styles to get weekend weekday zebra lines
• More sources about tables:
  • Create a table in Excel – Video Tutorial
  • 23 things to know about Excel Tables
  • Customizing table features (turn-off auto formula fill down etc.)

The post Excel Tables Tutorial & 13 Tips for making you a Data Guru appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

• What is the maximum profits we can make?
• What is the best way to schedule employees in shifts?
• What the best combination of tasks we can finish in a given time?

You might have heard about Excel Solver tool while trying to find solutions to questions above. If you have never used Solver or have little idea about it, then this post and video are for you.

What is Excel Solver really?

Excel Solver can solve problems for you. That simple!

For a given problem, excel solver can run various permutations and combinations and find out best possible solution for you. It is like goal seek, but better & awesomer.

Excel Solver Tutorial:

It is tricky to explain what solver does in text. So I made a short video (13 min). In this video you can learn,

• What is solver really?
• How to use it – a simple example.
• Finding the next month with 5 Fridays, 5 Saturdays and 5 Sundays using Solver

Watch the Excel Solver Tutorial video:

Few Tips while using Solver:

• Define your problem first: In order to make the best use of solver, you need to define your problem very clearly and model it using Excel. This comes with practice.  Start by modeling sample problems you find in work / life and you will be able to master this art.
• Tweak Solver Settings: Mess with solver settings by clicking on “options” button.

Additional Resources on Excel Solver

• Excel Solver Introduction and examples from Microsoft
• Excel Solver Examples from Vertex42
• Linear Programming with Solver from Economics Network
• Excel Solver Tutorial & Examples [PDF] from Standford University

What is your experience with Solver?

First I must confess that I am still a partial solver virgin. I have used it during my MBA to solve some optimization problems. Then I never had the opportunity to use it while working. I still struggle to set-up models and find required solutions thru Solver. That said, I think solver is an excellent tool and very powerful.

What is your experience with solver like? Please share tips / ideas with us using comments.

The post Are you a Solver Virgin? Watch this tutorial video …, appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Lets say you have data as shown below to left and you need to show zebra lines whenever the value changes (see right).

Your home work is simple. Just figure out how to write conditional formatting rules to add zebra lines.

The data set is here.

Go..

PS: Problem inspired from this post on Chandoo.org forums. Don’t cheat.
PPS: Here is some beautiful help on conditional formatting.

The post Show Zebra Lines when Value Changes [Excel Conditional Formatting Homework] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

What our mouth-watering chart will look like when its done:

Ingredients:

Some data, Few check-boxes, IF formula and a dash of espresso

Instructions for preparation:

1. First get your data. Make sure its clean and arranged neatly, like below, in the range B4:E11.
   
2. Since our data has 3 series (sales, profits and number of customers), we will take 3 check boxes and place them somewhere on our worksheet.
   Insert check boxes from developer ribbon / forms tool bar (tip: show developer ribbon in excel 2007)
   
3. Now, we want the check boxes to tell whether to show or hide a particular series of data in the chart. So, link each check box to one cell, say C13, D13 and E13.
   
4. We will use IF formula to roast our data based on what the check boxes say. So, create a similar table and load it with IF formulas like this:
   =IF(C$13,C4,NA())
   
5. Finally, make a chart with the data in this new table you created.
6. Put everything together and neatly arrange with your favorite colors and labels.
7. Serve hot and see your boss drool.

Download the prepared chart:

You can download FREE dynamic chart template and serve it instantly.

More recipes on dynamic charts:

• Select & show one chart from many
• Make a chart that grows as you add data
• Dynamically group related events in a chart
• More Dynamic Charts

Do you use dynamic charts?

I like dynamic charts a lot. They provide a wealth of information in a compact form. I use them whenever possible, especially in dashboards and analytical outputs.

What about you? Do you use dynamic charts often? What techniques do you use when implementing dynamic charts? Share your experience and tips using comments.

The post How to cook a delicious dynamic chart that will have your boss drool appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

That is, you have data like the numbers shown to right, with a surprising bump during Christmas time.

Now, how do you go about making a chart?

I can think of 4 options:

1. You make a chart as – is
2. Use log scale for vertical axis (axis option > check logarithmic scale)
3. Split the axis in to two: This is the most trickiest technique. I have used 2 charts and aligned them neatly. Thankfully Peltier Man comes to rescue. Read broken y-axis to know how to do this.
4. Let the bars reach sky: You can of course, set axis max to an arbitrary value, just so that all the small values are clearly displayed and let the higher values literally cut thru plot area.

Download these Examples:

I have put all these 4 chart examples in a workbook so you can see the underlying technique. Go ahead and download it here:

Excel Charting options when you large and small values [XLS]

What would you do?

I seldom deal with data that has this kind of outliers. But I am sure you get such data once in a while. How do you go about charting then? Share your ideas and implementations using comments.

More Charting Principles & Techniques.

Related discussion on Chandoo.org forums – Lots of small values but few extreme values – how to present it?

The post How do you make charts when you have lots of small values but few extremely large values? [Debate] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.