All articles in 'Learn Excel' Category
Congratulations to you if your job does not involve dead lines. For the rest of us, deadlines are the sole motivation for working (barring free internet & the coffee machine in 2nd floor, of course). So today, lets talk about a very familiar problem.
How to highlight due dates in Excel?
The item can be an invoice, a to do activity, a project or anything. So how would you do it using Excel?Continue »
Back when I was working as a project lead, everyday my project manager would ask me the same question.
“Chandoo, whats the progress?”
He was so punctual about it, even on days when our coffee machine wasn’t working.
As you can see, tracking progress is an obsession we all have. At this very moment, if you pay close attention, you can hear mouse clicks of thousands of analysts and managers all over the world making project progress charts.
So today, lets talk about best charts to show % progress against a goal.Continue »
Do you run an e-commerce website? You are going to love this simple, clear and easy website metrics dashboard. You can track 15 metrics (KPIs) and visualize their performance. The best part, it takes no more than 15 minutes to setup and use. Here is a preview of the dashboard.
Click to download the template.Continue »
Welcome to annual Easter Egg hunt at Chandoo.org. This year I have a feast for you. I hid 20 Easter Eggs in this workbook. Go ahead and find them. Share your solutions, clues or struggles in the comments box.
Happy hunting.Continue »
Excel Dynamic Array Functions are a true game changer. These newly introduced DA functions can filter, sort, remove duplicates and do much more. The output of these functions can go to a range of cells. Hence the name – dynamic array functions.Continue »
We can take any Excel workbook and format it until Christmas, and we would still not be done. But not many of us have so much of time or energy. So, today, lets talk formatting.
In this, you will learn how to
1) Use tables to format data quickly
2) Change colors of your worksheet in a snap
3) Use cell styles
4) Quickly clone formatting using format painter
5) Clear formats to begin with a clean-slate
6) Formatting shortcuts
7) Formatting options for print
and 8 ) Why you should not go overboard formatting and 10 other tips.
So go ahead and become a formatting pro.Continue »
Excel pivot tables are very useful and powerful feature of MS Excel. They are used to summarize, analyze, explore and present your data. In plain English, it means, you can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.
In this tutorial, we will learn what is a pivot table and how to make a pivot table using excel.Continue »
Ever wanted to make a random sentence or text? You can use Excel formulas to make totally random sentences. This is a great way to generate test data or dummy data-sets.Continue »
Happy New Year to all our readers. Let’s kick start 2020 with an awesome calendar, daily planner and monthly calendar template. Please visit the post page to download the 2020 Calendar Excel Template.Continue »
Hello readers, I have an exciting announcement for you.
I will be presenting at two events at London in April 2020. Please consider this my personal invitation and join me for Excel + Power BI fun.Continue »
Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. In this page, learn how to,
- Introduction to IRR & XIRR functions
- Calculate rate of return from a set of cash-flows with XIRR
- Simulating purchase or terminal value changes with data tables
- Apply conditional formatting to visualize the outputs
- Common issues and challenges faced when using XIRR
Validating your data as you type can prevent any surprises when you are doing analysis / follow-up on the data. Thankfully, excel has the right tools to do it. Excel drop down list can assist you in picking up a value from a valid list to enter in a cell. Here is a short how-to […]Continue »
Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.
What is weighted average?
Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”
Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.Continue »
This is CRAZY!!!. I stumbled on a weird use for FILTERXML() while reading a forum post earlier today. So I couldn’t wait to test it. I am happy to share the results.
Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the nth word. Unfortunately Excel doesn’t have SPLIT() formula. So we end up writing obscenely long array formulas or use gazillion helper columns.
Here is the super sneaky trick. Use FILTERXML() instead.Continue »
This is part of our Excel Interview Questions series.
VLOOKUP or INDEX+MATCH? When you should use each function and why?
This is such a great question to ask in interviews. So in my first installment of Excel interview questions, let me answer it.Continue »