Follow Chandoo.org - Learn Excel & Charting Online on Feedspot

Continue with Google
Continue with Facebook


Ever wondered how to undo in Power Query. If you try to press CTRL+Z or look for undo icon in Power Query (either in Excel or Power BI), you will not find it. The reason is simple. There is no undo in Power Query. So how to undo ?

Undo your actions in Power Query

To undo a step, simply select the step and press X (red cross mark) symbol next to it. That is all. Your step is undone. If you would like to undo a series from mid-way thru end, right click on the step and choose “Delete until end”.

How to Undo in Power Query – Video

Here is a simple video where I explain how to undo things in Power Query (you can also get a free tour of Melbourne)

I hope you enjoyed this little tip and the tour of Melbourne.

More videos & tutorials on Power Query

If you want to learn more about Power Query, check out below tuts:

The post How to undo in Power Query [Quick Tip] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Excel date time features are very handy and knowing how to use Excel date values can help you save a ton of time in your day to day spreadsheet chores. Let us prepare for your date with the sheet using these 10 handy tips.

Before jumping on to the tips, it helps to know how excel represents the date and time.

Microsoft Excel stores dates as sequential numbers … January 1, 1900 is serial number 1, and 20 June, 2018 is serial number 43271 because it is 43,271 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day. [Excel Help Text on Date / Time]

So you see, Date and Time are in fact numbers in Excel. Just enter a date in your excel sheet and format it as number to see its equivalent numeric value. If a date is 20-June-2018 and excel represents it as 43271.

Similarly, 9PM on 20-June-2018 is represented as 43271.875

How-to use Excel date values and formulas

Now that you know the little secret behind date / time, lets move to the 10 tips.

1. Test whether a date is future or past

You can find whether a date is past or future or today using simple if formula like: =if(this_date=today(),"Today",if(this_date < today(),"Past","Future"))

today() is the spreadsheet function using which you can find today’s date.

2. Find the number of days between two dates

Since dates are represented as sequential numbers in excel, in order to find out how many days are between any given 2 dates, just subtract one from another. For eg. you can use =today()-date(1947,8,15) to find how many days since India’s independence (August 15, 1947).

3. Formatting dates

Having date / time in the sheet is not enough if you can not make it look like the way you want. For eg. you may want to show date as “Wednesday, 20 June, 2018”. You can use cell formatting to do this. Just select the cell with date and hit ctrl+1 and in the “Number” tab select “Custom” as category and mention “dddd, dd mmmm, yyyy” as format string.

Try these other date formats as well.

Learn more about custom cell formatting.

4. Auto-filling only weekdays

We all know that in order to fill a series of dates in Excel sheet, you just need to enter first few dates and then select the range and drag to auto fill the selection with rest of the dates. But what if you need to fill only weekdays?

You can do that easily with Auto fill option – “weekdays only” as shown on the right. learn more.

5. Find out the day of week from a given date

Finding whether a day is weekend or weekday is useful if you are making project plans or resource allocation sheets. You can do this by simply using weekday() function. For eg. =weekday("06/20/2018") would return 4 (Excel, by default starts the week at Sunday, hence Wednesday is indicated as 4).

If you would like to start the week with Monday like most of us do, use =weekday("06/20/2018",2).

6. Highlight weekends using conditional formatting

Often when you are making project plans or reports, it helps if the weekends or after office hours can be grayed out. You can do this easily with conditional formatting as shown below:

In order to do this, we can test whether a given a day is weekend or not in conditional formatting by =WEEKDAY(this_date,2)>5 as weekday() returns 6 and 7 for Saturday and Sunday.

You can use similar logic to highlight after office hours (before 9AM or after 5PM) for time values. Learn these 5 tips to master conditional formatting.

7. Adding / Subtracting dates

Since Excel dates are nothing but numbers, you can find out the difference between two given dates by just subtracting one from another. For eg. =DATE(2018,7,31)-DATE(2018,7,1) will return 30

In order to add n number of days to a given date, you can just add that number to given date. For eg. ="07/20/2018"+26 will return 08/15/2018

8. Ensuring a valid date or time is entered in a cell

When sharing your sheets with others to enter some data, it may be useful if you can restrict them to enter only valid date values in cells that require date value. You can do that using cell data validation feature in excel. Just select the cell to which you want to apply date / time validation, go to data ribbon > validation and set type as “Date” or “Time” and specify criteria.

For example, you can specify criteria like the one above to ensure that date entered is in year 2018. What more, using message option of data validation settings you can even show messages like this:

9. Insert today’s date, current time using key board shortcuts

Just go to the cell where you want to insert date and press ctrl+;

To get current time, use ctrl+shift+; ( thus ctrl+: )

Btw, if you are planning to get today’s date or current time using formulas, you can use today() and now(). Also learn these 11 very useful excel keyboard shortcuts.

10. Top Date functions for you

Excel has many Date & Time functions. Here is a list of some of the most important ones to help you use date values in Excel.

Date & Time formulas
To getUse thisExample ResultFunction used
Day of week numberWEEKDAY(date)4WEEKDAY()
Month numberMONTH(date)6MONTH()
Day numberDAY(date)20DAY()
Name of the monthTEXT(date,"MMMM")JuneTEXT()
Same day, next monthEDATE(date,1)20 July 2018EDATE()
End of the monthEOMONTH(date,0)30 June 2018EOMONTH()
Current dateTODAY()20 June 2018TODAY()
7 days from todayTODAY()+727 June 2018TODAY()+7
Gap between two datesTODAY()-DATE(2018,1,1)170- (minus)
5 working days from nowWORKDAY(TODAY(),5)27 June 2018WORKDAY()
Number of working days in a monthNETWORKDAYS(DATE(2018,7,1),DATE(2018,7,31))22NETWORKDAYS()

That is all, with these 10 tips I hope I made your date with that spreadsheet is made little exciting.

Common problems when working with dates in Excel

When using date & time related values or formulas in Excel, often you might notice some problems. Use this check list to fix the problem.

  1. Excel shows ##### instead of date or time values
    This can happen if your cell is too small to show the value. Try adjusting column width.
    This can also happen if you use incorrect values as date & time. For example, if you try to format negative numbers as date, you will see #####
  2. Excel cannot understand my date
    When trying to convert a cell or value to date, sometimes Excel cannot understand your input. This is because Excel relies on your regional settings to understand dates. So if your usual date format is mm/dd/yyyy, then Excel expects the cells (or values) to have same format in order to convert them to dates. If you have dd/mm/yyyy values, then Excel may not convert the dates. To fix the problem, read extract dates from text tutorial
Download Date How-to & Tutorial Workbook

Click here download example workbook with several date calculations and format detail. Play with it to learn more.

Learn more about Excel Date & Time functions Got a problem working with dates? Post it in comments

If you have any date or time related issues, please post a comment so our community or I can help you. Got an interesting tip or formula about working with dates? Please do share it so I can learn from you.

The post How to use Date & Time values in Excel – a handy guide appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Time for some soccer fun in spreadsheets. Given a team points table like below:

How would you figure out in Excel, which teams qualify for next stage. The rules for this exercise are,

  • Pick the top two teams by points
  • If there is a tie, use Goal Difference (GD) to break ties (more GD is good)
  • If there is a tie, use Goals For (GF) to break ties

Note: these rules are only a sub-set of actual FIFA rules. But hey, we are not running soccer competition here. Just having fun, so let’s keep it simple.

In the above example, your answer should be 1 & 2 against Brazil and Mexico in column G.

Download this example points tables and use it to figure out the solution.

You may use formulas, VBA or Power Query to figure out the answers. If using Power Query, assume the input is just a 4 row table with column names, as shown above.

Go ahead and post your answers in the comments section. I am waiting to see all creative ways to attack this problem.

Want more soccer fun? More Excel challenges & homework problems

The post Which two teams qualify to next stage? [Excel Homework] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in Excel 2003) is a very powerful and super-cool feature that you must learn if your work involves handling tables of data.

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

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

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

So you have decided to up your game with Excel and / or Power BI this year and now ravenously looking for books to read. You have come to the right place. Here is my list of recommended best Excel books, and books on Power BI, visualization, dashboards, VBA, Macros and analytics.

Use below links to navigate the relevant section of this page:

Best Excel Books Excel 2016 Bible by John Walkenbach

If you are new to Excel or have used it for a while, you can’t go wrong with John’s tome. This 1,100 page tome covers everything from getting started with Excel to some nifty advanced use cases. It is a good reference book to have on your desk or in the team library. Despite its size, this is an easy read.

Excel 2016 by John Walkenbach on Amazon

Microsoft Excel Data Analysis & Business Modeling by Wayne Winston

I bought Wayne’s book in 2013 (same book, for Excel 2013) to learn all about how to apply business modeling, analytics and statistical analysis techniques in Excel. This is a very practical and insightful book and a must have for anyone working in advanced analytics, reporting, modeling or optimization space.

Microsoft Excel Data Analysis & Business Modeling on Amazon

Data Smart by John Foreman

Data Smart teaches all about data science, machine learning, statistical analysis and business optimization and then shows you how to implement fancy algorigthms using Excel. I recommend this book to anyone in the space of analytics or reporting. John has excellent penmanship and wicked sense of humor. You will find it hard to put down this book. I read and re-read this book several times.

Data Smart on Amazon

Best books on data & visualization Storytelling with Data by Cole Nussbaumer Knaflic

This book is on my to-read list. Many of us are in the profession of story telling and communication. Working with data and coming up with effective storytelling is vital for our success. Storytelling with Data gives you that guidance and makes you a better professional.

Storytelling with Data on Amazon

Information Dashboard Design by Stephen Few

Stephen Few’s classic book on dashboard design is what helped me become an expert in making awesome dashboards. I have the previous edition of his book and refer to it every now and then when I need some inspiration or smack on the back for going with a stupid design. Read it cover to cover and implement as much as you can.

Information Dashboard Design on Amazon

Best Power BI Books

Now things get tricky. Power BI is a rapidly evolving software. So any book on it, is bound to be outdated in a month’s time. I learned all my Power BI by experimenting, reading blogs and watching YouTube videos. That said, Power BI is not one software. It is a combination of three different tools:

  1. Power Query to process and clean data
  2. Power Pivot for modeling & analyzing data
  3. Power BI Visuals for creating outputs

I think parts 1 & 2 of Power BI puzzle (Power Query & Power Pivot) are the hardest things to learn. This is where books can help you tremendously. Since you will be learning the technique and language more than screen features, a book on Power Query or Power Pivot tends to have longer shelf life. So keep this in mind where reading my book recommendations.

Beginning Power BI by Dan Clark

Power BI is a new & rapidly changing technology. Hence, getting a reliable book on it is hard. Someone recommended this to me and I find the topics quite relevant and useful for a new Power BI user.

Beginning Power BI on Amazon

Power Pivot and Power BI by Rob Collie & Avi Singh

Rob & Avi do a good job of explaining Power BI & Power Pivot for traditional Excel users. If you are coming from Excel background and want to learn Power BI, pick up this book. Get the kindle copy as this uses eTextbook format which works very well on your tablet or PC.

Power Pivot and Power BI on Amazon

Learn to write DAX by Matt Allington

DAX can be very daunting and gnarly when you are getting started. I find Matt’s book approachable and elegant. This will help you overcome your fear for DAX and make useful calculations out of your data. Use it to take your reporting to next level.

Learn to write DAX on Amazon

M is for (Data) Monkey by Ken Puls & Miguel Escobar

This cheekily named guide will help you navigate the murky, unexplained waters of Power Query. That said, the book is 3 years old, so it is WAY behind times when it comes to what is new & better in Power Query. Microsoft has rapidly changed what & how of Power Query in the last 18 months and some of the techniques in the book feel outdated. Nevertheless, a good book to have. Get the kindle version, that way if Ken decides to upgrade the book, you will have new content for free.

M is for (Data) Monkey on Amazon

The Definitive Guide to DAX by Marco Russo & Alberto Ferrari

The Italians book is gold standard when it comes to writing ninja level DAX. Unfortunately, this book is not what you need if you are a beginner. Start with either Matt’s book or Rob’s book and graduate to this one. It will help you understand inner workings of DAX and make it your genie to pull of awesome magic from your data.

The Definitive Guide to DAX on Amazon

Best Excel VBA & Macros books Excel VBA Programming for Dummies by John Walkenbach

John’s book is the must have if you want to learn VBA programming in a comprehensive, clear way. It comes with many practical exercises, tips & best practices. I have the 2010 version of this and it helped me immensely when I was learning VBA programming.

Excel VBA Programming for Dummies on Amazon

Excel 2016 Power programming by Mike Alexander & Richard Kusleika

Mike & Dick are known for their generosity and creativity in Excel space, naturally this become reflects that. It is a treasure trove of good tips, examples and code base.

Excel 2016 Power programming on Amazon

My *own* books

Hey, I am an author too. I like to toot my horn once in a while. So if you are looking for books to polish your skills, consider these two.

PS: I am in the middle of writing my first Power BI book. This will be a beginner’s guide to everything Power BI. Watch out for an announcement about this.

The VLOOKUP Book by Chandoo

Learn everything from simple vlookup to complex INDEX, MATCH combinations in one book. Don’t be fooled by the name. This one goes beyond VLOOKUP and teaches you many powerful formula techniques when it comes to looking up data.

The VLOOKUP Book on Amazon

Excel Dashboards Book by Chandoo & Jordan Goldmeir

Create powerful, insightful and effective business dashboards using this handy guide. Learn all about charting best practices, VBA driven automation and more in this book. Also learn how Power Query and Power Pivot can revolutionize your workbook’s calculation engine.

Excel Dashboards Book on Amazon

What is your favorite Excel or Power BI Book?

Of course this list won’t be complete without your recommendation. So tell me dear reader, what is your favorite Excel or Power BI book? Post your answers in the comments section.

Note about the links in this page:

I am using my Amazon affiliate links in this post when recommending the books. That means, if you click on the links and make a purchase, I will receive a small commission from Amazon. This has no impact on my recommendation. I have personally read most of the books and recommend them even if there is no commission involved.

The post Best Excel Books & Power BI Books – 2018 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

FIFA world cup 2018 is around the corner. I love soccer, I love Excel, Let’s marry them. Here is an awesome, free FIFA world cup Excel Tracker to help you follow this year’s games in Russia.

Click here to download the FIFA world cup 2018 tracker.

What you can do with this FIFA world cup Tracker Excel?

You can use this tracker to,

  • View schedules in your local time for group and knockout stages
  • View summary and detailed points table
  • Refresh live points table. When you refresh, the tracker show updated points based on latest results (You need Excel 2016, Office 365 or older versions of Excel with Power Query)
  • View knockout stage matches as a bracket
  • See timeline of the matches
How to use the world cup 2018 tracker? Start page – set up the tracker

When you open the workbook, use “Home” page to set up your local timezone and favorite teams.

Group Stage – Schedule & summary points table

The group stage view shows all teams. It highlights your team(s) in different color to see when their games will be on.

On this page, you can also see group-wise points table. This is a live table. So as the matches happen, just press CTRL+ALT+F5 to update this table. The data comes from Wikipedia FIFA 2018 page thru Power Query. So you need Excel 2016 / Excel 365  or an older version of Excel with Power Query to get the refresh work.

Bracket View – Knockout Schedule

The knockout stage shows last 16, quarter-final, semi final and final matches in bracket view. This page is not dynamic. So as group stage finishes, go ahead and list the team names in the gray cells. 

Timeline View

This shows all matches in a Gantt chart like view. From this you can track all your favorite team matches. It will also highlight current date so you know which matches are coming up next.

Download FREE FIFA world cup tracker

Click here to download the FIFA world cup 2018 tracker.

Which teams are you rooting for?

As usual, I am cheering for Germany and Brazil. I will also watch out how Denmark & Sweden (my former homes) are going to play.

What about you?

The post FIFA Worldcup 2018 Excel Tracker – FREE Download appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Excel SUMIFS function is used to calculate the sum of values that meet any criteria. For example, you can calculate the total sales in east zone for product Pod Gun using SUMIFS formula.

In this article, you will learn:

  • What is SUMIFS function and how to use it?
  • Syntax for SUMIFS
  • Using SUMIFS() with tables and structural references
  • SUMIFS examples – simple, wild card
  • Using SUMIFS() with date & time values
  • Free sample file for SUMIFS formula
  • More formulas for data analysis
How to write SUMIFS Formula?

Using SUMIFS you can find the sum of values in your data that meet multiple conditions.

So, to get the sum of all the Blow Torches sold in North, we just write,

=SUMIFS(D3:D16, B3:B16,"Blow Torch",C3:C16,"North")

Similarly to find the podgun sales in East, just write,

SUMIFS function – Syntax and explanation:

SUMIFS formula takes a range for summing the values and at least one criteria range and criteria. You can specify as many as 127 conditions for summing your data.

Imagine asking “how many spit bombs Hansolo sold in North region of Planet Naboo between long long ago and long ago that resulted in more than 25% profits?” and getting an instant answer.

The beauty of SUMIFS formula is that it works with wildcards too, just like its siblings – SUMIF and COUNTIF. So you can write formulas like,

=SUMIFS(D3:D16,B3:B16,"Spit Bomb",C3:C16,"*th") to get sum of spit bombs sold in North and South.

Using SUMIFS() with tables

You can write SUMIFS function on either a range of data or on a table. When using with tables, you can simply apply structural references – ie TableName[Column Name] notation to specify the criteria columns. See this example:

SUMIFS Examples

Let’s say you have a table named ACME as pictured above. See these examples to understand how the function works.

  • Sales for Blow Torch in West
    • =SUMIFS(acme[Sales], acme[Product], "Blow Torch", acme[Region], "West")
  • Total Sales above 150 in East
    • =SUMIFS(acme[Sales], acme[Sales],">150",acme[Region],"East")
  • Sales of North for all excluding Pod Gun
    • =SUMIFS(acme[Sales], acme[Region],"North",acme[Product],"<>Pod Gun")
  • Sales of all products that contain letter B
    • =SUMIFS(acme[Sales], acme[Product], "*B*")
Using SUMIFS() with Date & time values

When you have a column of dates, you can apply special operators like >, <, =, <> to specify a date range.

For example, to count total sales between March 2018 and May 2018, we can use

=SUMIFS(acme[Sales], acme[Sales Date],">=1-Mar-2018", acme[Sales Date], "<=31-May-2018")

You can either type the date in the formula or bring it from a cell. If you have two cells containing start and end date for your window of dates, you can use this formula.

=SUMIFS(acme[Sales], acme[Sales Date],">=" & start_date_cell, acme[Sales Date], "<=" & end_date_cell)

Replace start_date_cell and end_date_cell with actual cell references or names.


Just like SUMIFS, there is COUNTIFS and AVERAGEIFS too in Excel. Once you know SUMIFS(), you can use all these other functions with ease.

SUMIFS Examples – Sample Workbook

If you want to learn more about SUMIFS function and practice the formula, download Free SUMIFS Example workbook. Play with the formulas to learn more.

Top 10 formulas for data analysis

Learning and using Excel formulas correctly is the key to success when it comes to your career as an analyst. If you enjoyed this post, check out my top 10 formulas for analysts page for more tutorials.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Ever wanted to count distinct values in your pivot tables? Something like this:

Here is a simple trick to add distinct count to Excel pivot tables easily.

Let’s say you have data like this:

As you can see, several products are repeated on each day. When you make a pivot table from this data and add product count, Excel counts all products. But we want to see just the distinct count (ie if there is a duplicate product in a day, we want to count it just once). To get distinct count in the pivot table,

These instructions work only in Excel 2016, Office 365 and Excel 2013. 

  1. Insert a pivot table from your data
  2. In the create pivot dialog, enable “Add this data to data model” option.
  3. Once you have the pivot table canvas, add the product (or any other field for which you want to calculate distinct count) to the values area.
  4. Right click on the values, go to “Value field settings”.
  5. Summarize the value by “Distinct count”. This is the last option.
  6. All done!
Distinct Count in Excel Pivot Tables – Example Workbook

If you want to practice this or want to see this with an example, here is the workbook.

More Pivot Table Tricks
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Are you hungry from some Excel awesomeness? It’s launch time. I am very happy to announce that our most popular analytics online class – 50 ways to analyze data is opening up for enrollments in first week of May 2018. As the name suggests, 50 ways program is all about data and making sense of it. You will learn many ways to analyze, visualize and present data to your clients and superiors. Next time someone asks you, “Can you look at this data and tell us what is going on?” you can confidently say YES.

Learn all about 50 ways program and signup for the waiting list.

In this article, Let me show case one of the examples from 50 ways to analyze data course and how it can help you.

Time Series Analysis and Forecasting in Excel:

Time series analysis refers to the concept of using historical values to understand trend and seasonality in your data and forecasting future values. In earlier versions of Excel, doing this meant building a forecast model that isolates two components of time series data – seasonality and trend. Thanks to new features added in Excel 2016, we can analyze and visualize time series data with ease. In an earlier article, I have discussed about the forecasting feature of Excel.

You can easily extend the built-in FORECAST.ETS() function in Excel 2016 to create something awesome like this:

I have just recorded a video explaining the process of creating such a chart in Excel. This is now part of our 50 ways to analyze data online class. You can get the video by joining our course waiting list.

Please sign up below and I will email the video and workbook to you in a day. Click here if you can’t see the form.

Already a student? You can see this lesson in the bonus section of classroom.

Thank you

Thank you for your continued support, love and inspiration. I am eagerly looking forward to re-opening the 50 ways program and share the knowledge with more of you.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

We all know that learning a few keyboard shortcuts can speed-up your Excel game. Most pro users rely on a handful shortcuts when working with large spreadsheets. But when it comes to charting, we automatically reach for mouse. But do you know that you can use few simple shortcuts to do most day-to-day chart related things?

Ready for top 5 keyboard shortcuts for Excel charts? Read on.

Format any part of chart with CTRL+1

If there is a contest for most memorable and super helpful shortcuts, then CTRL+1 would win it. It is easy to remember and it is versatile. Select any part of a chart and press CTRL+1 to format it. In older versions of Excel, this would launch format dialog. In newer versions of Excel, you will see the format pane to the right.

Copy formatting from one chart to another with ALT+E S T

This is one of those sequence shortcuts. If you want to copy the formatting of one chart to another, simply select first chart and press CTRL+C. Now select the second chart and press ALT E S T (your press ALT E, leave both keys and press S followed by T in a sequence) and voila your formatting is replicated.

See this demo:

Add new data to a chart with CTRL+C, CTRL+V

Who could forget the trusty copy and paste shortcuts. But do you know that you can use them with charts too? Yes, if you want to add extra series to your  chart, simply copy the data, highlight the chart and press CTRL+V to add it.

Repeat last action with F4

When you are working with lots of charts, often we repeat various formatting / customization steps on all of them. You can use F4 to speed up the process. Say you want to change axis color of all charts to dull gray. Do it one, now select the other axes and press F4.

Select various chart elements with TAB key (arrow keys in older version)

When you want to highlight various parts of the chart (title, series, axis, legend etc.) simply use TAB key (or arrow keys in older versions of Excel) to select one at a time. This way, you can avoid paying unnecessary mouse tax.

Bonus: Link chart title to a cell with = key

Nobody likes boring chart titles. So why not make them dynamic and awesome, just like you. Select the chart title, press = and link it to a cell. Of course, you need to have a formula (or typed up value) in the cell first. This way, when your formula changes, your chart’s title changes too.

Read more: Use descriptive titles for awesome charts

See this demo.

Top 5 Charting Shortcuts – Video

Here is a quick video demonstrating these shortcuts. Check it out below or on Chandoo.org YouTube Channel.

What is your favorite charting shortcut?

For me it is CTRL+1. I use it all the time. Next is ALT+EST. Anytime, I am working with more than one chart, chances are, I want to clone formatting.

What about you? What are your favorite charting keyboard shortcuts? Please share in the comments section.

More tips for working with charts and workbooks

Checkout below tips and hints to make better charts.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview