All articles with 'quick tip' Tag
#### in Excel cell
You open an Excel file and some cells show #### instead of the value. Here is a quick fix.Continue »
How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)
Want to connect SharePoint files to Power Query but keep getting “unable to connect” error? Here is a simple fix to the problem.Continue »
Filter one table if the value is in another table (Formula Trick)
I have an orders table and want to see only orders from a given list of products. We can use Excel’s FILTER function to do this.Continue »
Sorting values in Olympic Medal Table style [Quick Tip]
It is Olympic season. Everyone I know is tracking the games and checking their country’s performance. One thing that we notice when looking at medal tally is,
A single Gold medal is worth more than any number of Silver medals. Like wise, a single Silver medal is worth more than any number of Bronze medals.
So, when you look at the ranking of countries, you see countries with single Gold medal higher up than countries with lots of Silver and Bronze medals (but no Gold).Continue »
Two-level Data Validation [Excel Trick]
Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.Continue »
How to show positive / negative colors in area charts? [Quick tip]
Ever wanted to make an area chart with up down colors, something like this? Then this tip is for you.Continue »
Filter as you type [Quick VBA tutorial]
Filtering a list is a powerful & easy way to analyze data. But filtering requires a lot of clicks & typing. Wouldn’t it be cool if Excel can filter as you type, something like above.
Let’s figure out how to do this using some really simple VBA code.Continue »
Pivot Tables from large data-sets – 5 examples
Let’s say you are starting at a large dataset with multiple columns. You need to make a pivot report from it for a client or manager. How would you go about it?Continue »
How to sort left to right in Excel (quick tip)
Imagine you are in a life sustaining planet named Pearth, in another galaxy. One day you got to work, fired up Excel (hey, what else would you use? Excel is the best data software in any galaxy 🙂 ) and started working.
You came across a dataset that need sorting, but left to right – horizontally.
Now what? Do you turn your monitor sideways?Continue »
Quickly Change Formulas Using Find / Replace
So you have built that excel report your boss wanted. And you were all eager to use the spreadsheet in your presentation. But in the last minute, your boss asked you to change average sales to total sales figures. You also want to grab an espresso before rushing to the meeting. Now what?Continue »
Quickly change charts from one to another with this trick
We all change minds. Heck, I just did it with the leading line. I thought something else, but went with what you just read. So why must Excel charts be set in stone? Let’s say you made some charts and want to quickly second guess your selection. How to change Excel chart easily? Sure, you […]Continue »
Quick tip: Make a list of numbers (or dates) in Power Query easily
Just a quick tip to revive the blog from a month long silence. I am alive and kicking. I have been occupied with a quest to rescue princess & maidens on video game console. Recently we bought SNES classic console from Nintendo and I have been playing Legend of Zelda – a link to past regularly. As it is almost summer, I am also enjoying the beautiful outdoors in Wellington. All this means, little time for blogging. I will try to post a few more times before the end of year.
Make a list of numbers in a jiffy with Power Query:
We know that in Excel, you can type a few numbers and use the fill handle to fill down (or up etc.) numbers as you want.
But what if you need some numbers in Power Query?Continue »
Excel formula showing as text instead of actual result – How to fix the problem
Once in a while everyone is bound to come across this problem. You type a formula in a cell, then you press ENTER. Bam! nothing happens. You check if a donut chunk went in to the key board and some how jammed the ENTER key. So press it again, this time harder. But nothing. Excel […]Continue »
Distinct count in Excel pivot tables
Ever wanted to count distinct values in your pivot tables? Something like above:
Let’s say you have store sales data. Several products are sold 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).
Here is a simple trick to add distinct count to Excel pivot tables easily.Continue »
35 shortcuts & tricks to make you an #AWESOME Data Analyst
Analyst’s life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.
So today let me share with you 35 shortcuts, productivity hacks and tricks to help you be even more awesome.Continue »