fbpx

All articles with 'downloads' Tag

Visualize Excel Salary Data & You could win XBOX 360 + Kinect Bundle [Contest]

Published on Jun 25, 2012 in Excel Challenges
Visualize Excel Salary Data & You could win XBOX 360 + Kinect Bundle [Contest]

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.

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 »

Thermo-meter chart with Marker for Last Year Value

Published on Jun 11, 2012 in Charts and Graphs, Excel Howtos
Thermo-meter chart with Marker for Last Year Value

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

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 »

Formula Forensics 022. Sum the Odd Numbers between 1 and 100

Published on May 24, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 022. Sum the Odd Numbers between 1 and 100

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

Continue »

Highlight Employees by Performance Rating – Conditional Formatting Challenge

Published on May 18, 2012 in Excel Challenges, Learn Excel
Highlight Employees by Performance Rating – Conditional Formatting Challenge

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.

Continue »

Formula Forensic No. 021 – Find the 4th Slash !

Published on May 17, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensic No. 021 – Find the 4th Slash !

No, Not that Slash !

How do I find the 4th Slash in a text string?
Today at Formula Forensics we take a look at 4 different methods.

Continue »

VBA Move data from one sheet to multiple sheets

Published on May 14, 2012 in Automation, Excel Howtos, Learn Excel, VBA Macros
VBA Move data from one sheet to multiple sheets

Suresh sent an email with interesting problem.

There is one data entry sheet where all the data needs will be entered, however once done we want the data to be stored separately in multiple sheets designated by the Employee code.

In this article we will learn how to use VBA to help in resolving the problem Suresh was facing at work.

Continue »

Interactive Sales Chart using MS Excel [video]

Interactive Sales Chart using MS Excel [video]

Finally, I got some time to sit down and do what I love most – write a blog post to make you awesome in Excel. After a whirlwind trip to Sydney, I am back in India to spend few days with my kids & wife before rushing to Australia to run 2nd leg of my training programs (in Perth, Melbourne & Brisbane). I did 2 sessions in Sydney – one for KPMG and other for public and both went very well. We got lots of positive feedback and people really loved it. I am saving the details for another post, but today lets talk about Interactive Sales Chart using Excel.

Take a look at the Interactive Sales Chart

First, take a look at interactive sales chart. Today, you will learn how to build this using Excel.

Continue »

Displaying Text Values in Pivot Tables without VBA

Published on May 7, 2012 in Excel Howtos, Huis, Pivot Tables & Charts, Posts by Hui
Displaying Text Values in Pivot Tables without VBA

Pivot tables are a great way of summarising and consolidating data to produce summary reports.

One of the main limitations of Pivot tables is that they don’t natively return Text values.

This post looks at a method to work around this without the use of VBA.

Continue »

Adding Macros & Final Touches to Customer Service Dashboard [Part 4 of 4]

Published on Apr 26, 2012 in Charts and Graphs, VBA Macros
Adding Macros & Final Touches to Customer Service Dashboard [Part 4 of 4]

Welcome back. In final part of Making a Customer Service Dashboard using Excel let us learn how to add macros & VBA code that makes our dashboard interactive.

As you can see, there are 2 important macros in this dashboard.

Continue »

An IF Formula Challenge for you

Published on Apr 25, 2012 in Excel Challenges, Excel Howtos
An IF Formula Challenge for you

If I were to hire an data analyst, I would simply ask them to write a complex IF formula in Excel. If they can write it, the interview progresses, else, they are out. In other words,

=IF(person_can_write_big_fat_IF_formula=TRUE, proceed_with_interview, say_thanks_and_call_next_person)

If you are able to write IF formulas for any situation, then you are bound to be awesome in Excel.

So, to test how well you know your IFs & Boolean functions, let me give you a small challenge.

Continue »

Send mails using Excel VBA and Outlook

Published on Apr 23, 2012 in Automation, Excel Howtos, Learn Excel, VBA Macros
Send mails using Excel VBA and Outlook

Ever wondered how we can use Excel to send emails thru Outlook? In this article we well learn how to use VBA and Microsoft Outlook to send emails with your reports as attachment.

Scenario: We have an excel based reporting template. We want to update this template using VBA code to create a static version and email it to a list of people. We will define the recipient list in a separate sheet.

Read on…

Continue »

Formula Forensic No 019. Converting uneven Text Strings to Time

Published on Apr 19, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensic No 019. Converting uneven Text Strings to Time

I have imported some data that comes in as a number that I need to convert to h:mm. The data string will be either 1,3,4,5,6 integers long and looks like this…

Help

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 »