All articles in 'Learn Excel' Category
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 »
Excel pivot tables are very useful and powerful feature of MS Excel. They can be 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 »
Can we predict cricket match score in Excel? Using machine learning, ensemble modeling, multiple regression and Excel formulas we can. This tutorial explains how.Continue »
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 »
We all know that VLOOKUP can find first match and return the results. But what if you want all the matches? Use this simple trick instead.Continue »
Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.Continue »
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 »
We all know VLOOKUP (or INDEX+MATCH) as an indispensable tool in our Excel toolbox. But what if you want the lookups to be a little gentler, nicer and relaxed?
Let’s say you want to lookup the amount $330.50 against a list of payments. There is no exact match, but if we look 50 cents in either direction, then we can find a match. Here is a demo of what I mean.
Unfortunately, you can’t convince VLOOKUP to act nice.
Hey VLOOKUP, I know you are awesome and all, but can you cut me some slack here?
VLOOKUP is tough, reliable and has a cold heart. Or is it?
In this post, let’s learn how to do lenient lookups.Continue »
Do you want to learn Advanced Excel, data analysis, creative charting, dashboard reporting and be productive in Excel? Then you are going to love my Excel School v2.0.
This is the golden age of data. Any rewarding job or entrepreneurship requires good data literacy. We are all expected to understand the relevance of data, how to manage it, how to analyze it, visualize it and tell elegant stories.
This is where Excel School comes in.Continue »
Obviously, everywhere you look this week, you find advice on how to do better this year.
Well, you don’t have to wait for a new year to become awesome at your work, any day is new year for the rest of your life.
So, today I want to share 12 steps to learn Excel and becoming awesome. But first I have a secret to confess…,
I think becoming awesome in Excel useless.
What we really want is, to become awesome in our work. Since we spend a lot of time using Excel (and other office software), knowing how to use these better can have a huge impact on how we do our work.
In that spirit, lets look at 12 steps to learn Excel so you become awesome in your work.Continue »
I recently finished a long consulting gig with one of the government ministries in New Zealand. Guess what I was doing? HR Analytics and Reporting. In this post, I want to share my top 5 Excel tips for HR people, based on what I learned in the last 18 months.
Specifically, we will cover:
- Gathering and structuring Employee data in Excel
- How to use Power Query to collect data
- Polish / clean data in Power Query
- Bring cleaner data to Excel as refreshable table
- Answering questions about employees
- Using Excel formulas such as COUNTIFS, SUMIFS, AVERAGEIFS
- Pivot tables for data analysis
- Understanding the results quickly with conditional formatting
- Understanding pay gap
- Calculating gender pay gap
- Visualize pay gap
- Creating salary distribution charts
- Working with histogram charts in Excel 2016 / Office 365
- Making interactive charts
- Generating letters thru mail merge
- Calculating employee bonus based on bonus mapping logic
- Creating 100s of letters with a single click using Mail Merge + Word
Sounds interesting? Read on for details.Continue »