All articles with 'Learn Excel' Tag

Did I tell you I love tennis? Some of my personal heroes & motivators are tennis players. And as you can guess, I admire Roger Federer. Watching him play inspires me to achieve more. So last night when he lifted Wimbledon trophy for 7th time, I wanted to celebrate the victory too, in my style. So I made an interactive timeline chart in Excel depicting his victory.

Jo (wife) likes to watch Masterchef Australia, a cooking reality show every night. Even though I do not find contestant’s culinary combats comforting, occasionally I just sit and watch. You see, I like food.

The basic premise of the program is who cooks best in given time. To tell people how much time is left, they use a clock that indicates how much time is left (much like a stop clock, with a small twist).

One day, while watching such intense battle, my mind went

It be cool to make such a clock using hmm… Excel?

While I cannot share my snapper (or pretty much any other food item) with you, I can share my Masterchef style Excel clock with you. So behold,

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.

A while ago (well more than 3 years ago), I wrote about an array formula based technique to check if a list of values have any duplicates in them.

Today, lets learn a simpler formula to check if a list has duplicate numbers.

Assuming you have some numbers in a range B4:B10 as shown below, we can use MODE + COUNTIF formulas to check if there are any duplicate values in a list.

Last week we discussed how to extract numbers from text in Excel using formulas. In comments, quite a few people suggested that using VBA (Macros) to extract numbers would be simpler.

So today, lets learn how to write a VBA Function to extract numbers from any text.

Its contest time again! Put on your creative hats & bring your Excel skills to the game.

Analyze more than 1900 survey responses & present your results in a stunning fashion, and you could walk away with an XBOX 360 + Kinect Sports Bundle (valued at $299).

Sounds interesting? Read on.

Hello lovelies,

I have a quick announcement to make. As you know, we run 2 online courses on Financial Modeling.

1) Financial Modeling School
2) Project Finance Modeling School

After running them for almost 2 years, we are now planning to hike the fees by $50. See below table to know about new fees.

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.

Here is a quick tip that I learned while conducting training classes in Australia. If you have several dates in a range and you want to find out what the latest date is, just use MAX, like: =MAX(A1:A10) would give you the latest date. A Question…, Assuming you have some dates (not necessarily sorted) in […]

During a recent training program, one of the students asked,

Thermo-meter chart is very good to show how actual value compares with target (or budget). But how can we add another point for say Last Year value to the chart with out cluttering it.

Something like above.

Sounds interesting? Read on

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

Ever looked at a Pivot table & wondered how you can sort it differently?

“If only I could show this report of monthly sales such that our best months are on top!”

Well, there is a way to do it without sacrificing 2 goats or pleasing the office Excel god. Just use custom sorting options in Pivot tables.

Ever wondered how much your Excel skills are worth?

Here is 2 step procedure to find out.
1) Complete this survey. Tell me how much you make & what you do.
2) Wait for a week and we will tell you how much Excel professionals are earning all over the world 🙂

PS: You can be anonymous.

How can we Add Up the Odd numbers between 1 and 100 ?

So who is up for an Excel challenge?

Shelly, who is an HR Manager sent this distress call last week,

“I have a group of employees- lets say 100 employees. Each employee has a performance rating attached to them. I want to divide the group by 5%, 15%, 65%, 10%, 5% based on their performance rating.”

And that is our challenge today. Read on & help Shelly.