fbpx

All articles with 'sumproduct' Tag

Top 10 things I learned using Excel for a decade

Published on Jan 6, 2014 in Charts and Graphs, Learn Excel
Top 10 things I learned using Excel for a decade

Sometime during the 2nd half of 2013, I finished 10 years of Excel usage. In the last 10 years, I completed my studies, got my first job, married, had kids, visited 15 different countries, quit my job to start a business, bought first car, first house, made dozens of new friends, read 100s of books, wrote a book and learned 1000s of new things. And all along, Excel stayed a true companion. Right from MBA entrance exam preparation in 2003 to making my summer internship project reports in 2005 to planning my wedding expenses in 2007 to getting a promotion in 2009 to planning my kids feeding schedule in 2010 to running a successful business in 2014, Excel helped me in every step.

So today, I want to tell you the top 10 things I learned using Excel in last decade. Grab a hot cup of coffee, buckle your belts and get ready for time travel.

Continue »

How to find sum of top 3 values based on filtered criteria [video]

Published on May 17, 2013 in Excel Howtos
How to find sum of top 3 values based on filtered criteria [video]

Lets say you are looking at some data as shown above and wondering what is the sum of budgets for top 3 projects in East region with Low priority. How would you do that with formulas?

Continue »

How well do you know your LOOKUPs? – Quiz Answers & Discussion

Published on Apr 4, 2013 in Excel Howtos, Learn Excel, Quizzes
How well do you know your LOOKUPs? – Quiz Answers & Discussion

Last week, we had our very first quiz – “How well do you know your LOOKUPs?”. I hope you have enjoyed it.

Today lets understand the answers & explanations for this quiz.

Continue »

Top 10 Formulas for Aspiring Analysts

Published on Jan 16, 2013 in Learn Excel
Top 10 Formulas for Aspiring Analysts

Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.

While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.

If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.

A good analyst should be familiar with below 10 formulas to begin with.

Continue »

Formula Forensics No. 029 SumIf with Inconsistent Column Layouts

Published on Sep 20, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 029 SumIf with Inconsistent Column Layouts

How do we perform a Sumif on seemingly randomly spaced columns?
Today in Formula Forensics we look at a solution to do just that.

Continue »

Excel Links – Going to Togo Edition

Published on Sep 4, 2012 in excel links

After a long time, I am writing one more Excel links post. First let me share a quick personal update.

I am invited to Lome, Togo to spend a day at Ecobank – CFO conference in 3rd week of September. I will be conducting a one day masterclass on Advanced Excel & Dashboard Reporting. I am excited about this trip as I have never been to any African country. I will post some pics and tell you how it went once I am done with the conference.

Moving on to Excel Links for today,

Continue »

Making your dashboards interactive [Dashboard Essentials]

Published on Aug 2, 2012 in Charts and Graphs, Excel Howtos, Learn Excel
Making your dashboards interactive [Dashboard Essentials]

Everyone likes to be in control. Even my 2 year old daughter jumps with joy when she lays her hands on TV remote. She pushes the buttons and assumes it is working. It is another story that we rarely watch TV at home.

By adding an element of control, we can make our dashboard reports fun. Interactive elements like form controls, slicers etc. invite users to play with your dashboard, get involved and understand data by asking questions. That is why I recommend making dashboards interactive.

Today lets understand how you can make dashboards interactive.

Continue »

Formula Forensics 025. Count Unique Values in a Range

Published on Jul 26, 2012 in Formula Forensics, Huis, Posts by Hui, wonder why
Formula Forensics 025. Count Unique Values in a Range

Need to count how many unique values occur in a range?
Learn how to do this and how the formula works right here.

Continue »

Formula Forensics 024. Is this number a Prime Number ?

Published on Jul 12, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 024. Is this number a Prime Number ?

Is my number a Prime Number?
What is a Prime Number?

Today in Formula Forensics we answer both questions.

Continue »

Find the last date of an activity

Published on Jul 3, 2012 in Formula Forensics, Learn Excel
Find the last date of an activity

We know that using VLOOKUP, we can find a value corresponding to a given item. For example Sales of x. But what if you have multiple sales for each item and you want the last value?

Today lets understand how to find the last date of an activity, given data like above.

Like everything else in Excel, there are multiple ways to finding last date. If cats can use computers, they would hate Excel. You see, Excel is overflowing with unlimited ways to skin a cat.

Continue »

Extracting numbers from text in excel [Case study]

Published on Jun 19, 2012 in Excel Howtos
Extracting numbers from text in excel [Case study]

Often we deal with data where numbers are buried inside text and we need to extract them. Today morning I had such task. As you know, we recently ran a survey asking how much salary you make. We had 1800 responses to it so far. I took the data to Excel to analyze it. And surprise! the numbers are a mess. Here is a sample of the data.

Continue »

Formula Forensics 023. Count and Sum a Filtered List according to Criteria

Published on Jun 7, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 023. Count and Sum a Filtered List according to Criteria

Today at Formula Forensics, we look at how to Count and Sum data using Criteria on Filtered data sets.

Continue »

Creating Customer Service Dashboard in Excel [Part 3 of 4]

Published on Apr 18, 2012 in Charts and Graphs, Learn Excel
Creating Customer Service Dashboard in Excel [Part 3 of 4]

Welcome back. In third installment of our Customer Service Dashboard series, we will learn how to construct the charts in our dashboard. We will understand the sparklines, traffic lights & dynamic chart setup. To help you learn better, I have recorded a short video too. Go ahead and enjoy.

Continue »

Excel Speedup & Optimization Tips by Experts [Speedy Spreadsheet Week]

Published on Mar 26, 2012 in Learn Excel, VBA Macros
Excel Speedup & Optimization Tips by Experts [Speedy Spreadsheet Week]

As part of Speedy Spreadsheet Week, I have emailed few renowned Excel experts and asked them to share their tips & ideas to speedup Excel. Today, I am glad to present a collection of the tips shared by them. Read the Excel optimization & speeding up tips shared by Hui, Luke, Narayan, George, Gregory & Jordon.

Continue »

Speed up your Excel Formulas [Speedy Spreadsheet Week]

Published on Mar 20, 2012 in Excel Howtos, Learn Excel
Speed up your Excel Formulas [Speedy Spreadsheet Week]

Excel formulas acting slow? As part of our Speedy Spreadsheet Week, today lets talk about optimizing & speeding up Excel formulas. Use these tips & ideas to super-charge your sluggish workbook. Use the best practices & formula guidelines described in this post to optimize your complex worksheet models & make them faster.

1. Use tables to hold the data
2. Use named ranges & named formulas
3. Use pivot tables
4. Sort your data
5. Use manual calculation mode

… and more. Read on to learn these top 10 tips & ideas to improve performance of your excel formulas.

Continue »