All articles in 'Excel Howtos' Category

Highlight due dates in Excel – Show items due, overdue and completed in different colors

Published on May 22, 2012 in Excel Howtos, Learn Excel
Highlight due dates in Excel – Show items due, overdue and completed in different colors

Congratulations to you if your job does not involve dead lines. For the rest of us, deadlines are the sole motivation for working (barring free internet & the coffee machine in 2nd floor, of course). So today, lets talk about a very familiar problem.

How to highlight due dates in Excel?

The item can be an invoice, a to do activity, a project or anything. So how would you do it using Excel?

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 »

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 »

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 »

Compare 2 Excel Files using View side by side Mode [Quick Tip]

Published on Apr 16, 2012 in Excel Howtos
Compare 2 Excel Files using View side by side Mode [Quick Tip]

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.

Continue »

Consolidate data from different excel files (VBA)

Published on Apr 9, 2012 in Automation, Excel Howtos, VBA Macros
Consolidate data from different excel files (VBA)

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.

Continue »

Using Excel As Your Database

Published on Apr 2, 2012 in Automation, Excel Howtos, VBA Macros
Using Excel As Your Database

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.

Continue »

Speeding up & Optimizing Excel – Tips for Charting & Formatting [Speedy Spreadsheet Week]

Published on Mar 21, 2012 in Charts and Graphs, Excel Howtos
Speeding up & Optimizing Excel – Tips for Charting & Formatting [Speedy Spreadsheet Week]

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

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 »

This week, Speed up your Spreadsheets – Your Action Required

Published on Mar 19, 2012 in Excel Howtos
This week, Speed up your Spreadsheets – Your Action Required

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.

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 »

In Cell Text Formats – 2 Quick Tips

Published on Mar 6, 2012 in Excel Howtos, Huis, Posts by Hui
In Cell Text Formats – 2 Quick Tips

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.

Continue »

14 ways to check if an year is leap year, using Excel [just for fun]

Published on Feb 29, 2012 in Excel Howtos, Learn Excel
14 ways to check if an year is leap year, using Excel [just for fun]

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.

Continue »