fbpx
Search
Close this search box.

All articles with 'spreadsheets' Tag

Weighted Average in Excel [Formulas]

Published on Mar 14, 2024 in Learn Excel
Weighted Average in Excel [Formulas]

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 »

VLOOKUP(), MATCH() and INDEX() – explained in plain English

Published on Feb 11, 2024 in Featured, Learn Excel
VLOOKUP(), MATCH() and INDEX() – explained in plain English

VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, INDEX and MATCH worksheet formulas can transform you from normal excel user to a data processing beast. Today, […]

Continue »

Range Lookup in Excel – How to lookup the pricing tier? [Formulas]

Published on Dec 17, 2022 in Learn Excel
Range Lookup in Excel – How to lookup the pricing tier? [Formulas]

Excel formula to get a match from a given value in excel when you have start and end values in lookup table. This technique is useful for looking up a matching price from a pricing tier table or date in a range of start and end dates.

Continue »

Sorting values in Olympic Medal Table style [Quick Tip]

Published on Aug 2, 2021 in Excel Howtos
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 »

How to use Date & Time values in Excel – 10 + 3 tips

Published on Jul 28, 2021 in Excel Howtos, Learn Excel
How to use Date & Time values in Excel – 10 + 3 tips

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 28 July, 2021 is serial number 44405 because it is 44,405 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

Read on to learn more.

Continue »

18 Tips to Make you an Excel Formatting Pro

Published on Mar 19, 2020 in Excel Howtos, Learn Excel
18 Tips to Make you an Excel Formatting Pro

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 Tutorial : What is a Pivot Table and How to Make one

Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one

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 »

Quickly Change Formulas Using Find / Replace

Published on Feb 28, 2019 in Learn Excel
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 »

Introduction to Excel SUMIFS Formula

Published on May 25, 2018 in Learn Excel
Introduction to Excel SUMIFS Formula

Excel SUMIFS function is used to calculate the sum of values that meet any criteria. For example, you can calculate the total sales in east zone for product Pod Gun using SUMIFS formula.
In this article, you will learn:

  • What is SUMIFS function and how to use it?
  • Syntax for SUMIFS
  • Using SUMIFS() with tables and structural references
  • SUMIFS examples – simple, wild card
  • Using SUMIFS() with date & time values
  • Free sample file for SUMIFS formula
  • More formulas for data analysis
Continue »

Excel formula showing as text instead of actual result – How to fix the problem

Published on May 24, 2018 in Excel Howtos
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 »

A Spreadsheet walks in to a bar … [open mic]

Published on Sep 7, 2012 in Humor

Lets have some fun this Friday.

Post your favorite Spreadsheet jokes using comments. Have a big smile.

I will start:

1. A pivot table walks in to a bar and orders a beer. It says, “Put me in the same tab, will ya?”
2. A slicer and pivot chart walk in to a bar. The bartender says, “look at those bast*rds, walking around with out a pivot!”.
3. Once Excel, Access and Windows were bragging to each other. Excel says, “I once crashed so hard, it took 5 minutes to recover”. Access says, “Oh thats nothing. I once crashed and took down an entire data base. It took them 30 minutes to recover”. Windows doesn’t say anything. Excel pokes him in the arm asks “what about you?”, Windows jolts & replies, “Sorry, what did you say, I just crashed again.”

Continue »

What is so special about Go To Special? [15 tips]

Published on Mar 12, 2012 in Excel Howtos
What is so special about Go To Special? [15 tips]

We briefly covered Excel’s Go To Special function in the Managing Spreadsheet Risk series and in this post, we are going to explore Go to special feature in detail and learn how to use it.

What is Go To Special?

Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet. Once you get to grips with this function and what it can be used for you will wonder how you ever lived without it. Read on…,

Continue »

Announcing Online VBA Classes from Chandoo.org, Please Join Today

Published on Jan 11, 2012 in products, VBA Macros

VBA Classes from Chandoo.org - Learn Microsoft Excel VBA & MacrosFriends & Readers of Chandoo.org,

I am so happy to tell you that our VBA Classes are now open for your consideration. Click here to know more & join us.

What is this VBA Class?

VBA Class is a structured and comprehensive online training program for learning Microsoft Excel VBA (Macros). It is full of real world examples & useful theory.

The aim of VBA Classes is to make a beginner an expert in VBA.

Read on to understand the benefits of this program & how to sign-up.

Continue »

Compare 2 Lists Visually and Highlight Matches

Published on Oct 27, 2011 in Excel Howtos, Learn Excel
Compare 2 Lists Visually and Highlight Matches

Comparison is one of the most common things we do with Excel. Naturally, there are so many ways to compare 2 lists of data using Excel.

Today, I want to share an interesting comparison problem with you.

Lets say you run a small shop which sells some highly specialized products. Now, since your products require quite some training before customers can buy them, you keep track of all product queries and arrange demos.

After a hectic week, you are staring at 2 lists. One with product queries, another with product demos. And you want to know whether all the queries are answered with a demo or not.

Continue »

Split an Excel File in to Many using VBA [Videos]

Published on Oct 19, 2011 in VBA Macros
Split an Excel File in to Many using VBA [Videos]

Splitting an Excel file in to many is easier than splitting bill in a restaurant among friends. All you need is advanced filters, a few lines of VBA code and some data. You can go splitting in no time.

Context:

Lets say you have lots of data like this in a file. And you want to split this in to multiple files, one per salesperson.

Solution – Split Data in to Multiple Files using Advanced Filters & VBA

The process of splitting data can be broken down to 4 steps …,

Continue »