All articles in 'Excel Howtos' Category

Have you ever wondered about applying different Spreadsheet Formats or Styles to reports which you may be send to different people and so the styling may be different for each recipient?

I haven’t, but in this post I will show how you can add it to your worksheets.

Learn some Excel Tips, Tricks, Cheats & Hacks from some Notable [Non-MVP] Excel Websites.

Excel Tips, Tricks, Cheats & Hacks – Chandoo.org Excel Ninja Edition
Learn some of the Chandoo.org Excel Ninja’s favorite Excel Tips, Tricks, Cheats & Hacks

Learn some of the Microsoft Excel MVP’s favorite Excel Tips, Tricks, Cheats & Hacks in this post
Excel Tips, Tricks, Cheats & Hacks – Microsoft MVP Edition

Here is an interesting scenario.

Let’s say you are looking at a time, like 9:42 AM and want to know which 15 minute slot it fits into. The answer is 9:30 – 9:45. But how would you get this answer thru Excel formulas?

One of our readers emailed this question recently,

I like the conditional formatting icons. I am trying to present some business data where going down is good. How do I get a green colored down arrow icon?

Essentially, Ms. CanIGetItInGreen wants this:

Unfortunately, Excel’s conditional formatting icons are not customizable. So we can’t get the green down arrows without some sneak. And sneak we shall.

Lets look at how to apportion sales according to multiple criteria

In case, this is the first time you are hearing about Excel formula wildcards, check out the Using wildcards in Excel VLOOKUP formula tutorial.

So you know about wild cards like * ?, now how would you tell VLOOKUP to ignore them?

Say, you are genuinely interested in looking the value “* Payroll” in a lookup table. What then?

This is exactly the problem faced by Peter in our forum post VLOOKUP and cells with “*” NOT to be interpreted as wildcard

Here is an interesting twist on the good old VLOOKUP. How to find the pricing applicable for given quantity of a product?

Something like above.

Looks interesting? Then read on…

Let’s keep this simple & short.

Whenever you are editing cells or formulas, the usual sequence is like this:

1. Double click on the cell you want to edit
2. For existing cells: Go to the left most / right most part and start typing
3. For blank cells: start typing right away

Here is a faster sequence:

Read on…

If you deal with customers or colleagues in Europe, often you may see numbers like this:

• 1.433.502,50
• 9.324,00
• 3,141593

When these numbers are pasted in Excel, they become text, because Excel can’t understand them.

Here is a simple way to convert the European numbers to regular ones.

Use NUMBERVALUE() Function.

Imagine you are looking customer data like below and want to sort them by performance. If you sort the data by any one column, you will not get full picture of performance. To understand which customers rank low on performance, you need to defined a weighed sort, the kind of sort where you assign weights to each attribute (customer age, recent purchases and rate of returns) and come up with single score to sort them all.

Sounds interesting? Watch below video to understand how to do weighted sorting in Excel.

We all know that VLOOKUP (and its cousins MATCH, HLOOKUP and LOOKUP) are great for finding information you want. But they are helpless when you want to do a case-sensitive lookup.

So how do we write case sensitive VLOOKUP formulas?

Simple. We can use EXACT formula.

Cascading drop downs enhance usability of your dashboards & interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.

There are many ways to setup cascading drop downs. You can use formulas coupled with either data validation or form controls. You can also use Slicers. In this video we will review these techniques.

Yesterday, you learned about Print Areas – a time & paper saving feature of Excel. While print areas are great, you can only set up one print area per sheet. What if you want to print either report or data based on user selection? 

In such cases, you can set up dynamic print areas. 

That is right. See above demo to understand how it looks. Read on to learn how to set up dynamic print areas.