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:
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.
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.
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+: )
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
Day of week number
Name of the month
Same day, next month
20 July 2018
End of the month
30 June 2018
20 June 2018
7 days from today
27 June 2018
Gap between two dates
5 working days from now
27 June 2018
Number of working days in a month
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.
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 #####
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
Power Query to process and clean data
Power Pivot for modeling & analyzing data
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
Let’s say you have a table named ACME as pictured above. See these examples to understand how the function works.
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.
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.
Insert a pivot table from your data
In the create pivot dialog, enable “Add this data to data model” option.
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.
Right click on the values, go to “Value field settings”.
Summarize the value by “Distinct count”. This is the last option.
Distinct Count in Excel Pivot Tables – Example Workbook
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.
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 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.
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.