All articles in 'Excel Howtos' Category

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

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.

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.

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.

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.

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…

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

Often we have 2 workbooks with same data structure but different data. We want to compare both and see how they differ. Lets talk about view side by side mode in Excel and how we can use it in situations like these.

Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.

Often I have thought, if I could have write “Select EmployeeName From Sheet Where EmployeeID=123” and use this on my excel sheet, my life would be simpler. So today we will learn how to do this.

People spend a lot of time thinking whether to use Excel as their database or not. Eventually they start using Access or SQL Server etc.

Today we will learn how to use Excel as a Database and how we to use SQL statements to get what we want. We will learn how to build a form like above.

Is Excel acting slow & taking ages? As part of our Speedy Spreadsheet Week, today lets talk about optimizing & speeding up Excel by formatting & charting better. Use these tips & ideas to super-charge your sluggish workbook.

No matter how much data you got, how many formulas you wrote, the end users seldom see them on your workbook. They see the finalized dashboard, they play with the model, they look at the report. And if you make poor choices, your end users will thing your workbook is slow.

So let me present you 7 charting & formatting tips to optimize & speed up Excel. Read on…,

In recent installment of Customer Service Dashboard post, our reader Salmon asked an interesting question,

I am struggling with data size with my dashboards…so many SQL data pulls and formulas to generate the Dashboard, the entire file is massive and sluggish. Perhaps a few tips from Chandoo Master for all us rookie dashboard designers regarding how to minimize file size and maximize calc speeds. #

Dan l & others chipped in and shared their ideas on speeding up Excel. But the topic is wide & has many solutions. So I am dedicating an entire week to discuss this. Welcome to Speedy Spreadsheet Week.

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…,

Did you know you can apply any text effect to a single character or group of text characters within a cell ?

This post will show you 2 quick tips to assist you in this technique.

Today is February 29th, and that means, this year we have one more day to be awesome. So lets celebrate it in Excel style!

Lets learn 14 different ways to tell if an year is leap year, using Excel Formulas.

Why 14? because, we are awesome like that.