Excel Tutorial and Tips for Spreadsheet Analysts
Excel TV
Share The LOVE

Category Archives for Excel Tips & Tricks

Random Sorted Order without Repeats Using Excel Formulas

There might be times when you need to shuffle a list of distinct items. It can be to run a simulation analysis, to pick a random sample for statistical analysis or to just have some fun. All you have to do is generate random integers between 1 and the size of your list. And then […]

Continue reading

Make Nested IF Functions Readable With This Tip – Excel Tips

Most, if not all, pieces of analyses in Excel employ the IF function numerous times. In fact, it is one of those functions which analysts and Excel experts play with a lot. And, because of that, nested IF functions result much more often than one would imagine. Well, that doesn’t sound like a problem in […]

Continue reading

VLookup To The Left With The Choose Function – Excel Tips

We all know that VLOOKUP works to the right (of the column with the lookup value). But what if we want to the ability to lookup a value to the left? One solution is to use INDEX and MATCH together. Well, there is another, more elegant solution. And Szilvia Juhasz (aka XSzil) is here to […]

Continue reading

Paste Data To New Excel Worksheet – Excel Tips

  Done with your analysis and now you want to move your data somewhere else, but it’s too voluminous and just won’t move easily? Well, there might be many things keeping you from transferring the data. It might be that available RAM isn’t enough, the processor size just isn’t helping or it is just taking […]

Continue reading

Right Align & Indent Numbers in Excel – Excel Tips

Clean presentation is one of the marks of an Excel expert. If you know you’re good, why let sloppy presentation of your work ruin your perception? Look at the table on the right. A list of 5 numbers has been presented in 5 different formats. While the data is not aligned in columns A, C […]

Continue reading

Change Sheets Names – Excel VBA Tips

We all are aware of the basic methods of referencing worksheets in our VBA code. Two of such methods have been illustrated in the picture on the right. But there is one inconvenience in putting these methods to use: the differences between sheets is getting lost in the semantics. Our very own Jordan Goldmeier (a.k.a. […]

Continue reading

How To Run Macros In Protected Worksheets – Excel VBA Tips

We know that human error often messes up complicated calculations or elegant models set up in Excel. For this reason, many of us protect worksheets using “Me.Protect” within our macros. But there is a drawback in doing that. Protecting sheets disables macros from running!! Worry no more! Excel MVP Jordan Goldmeier aka Option Explicit is […]

Continue reading

Get a free bonus video when you purchase Dashboards

Until the end of September, Chandoo and I will be offering a free, one-hour bonus video with all purchases of Dashboards for Excel. Here’s how to qualify for this offer: Purchase a copy of the book form Amazon (you can also buy it from a bookstore, but I’m not sure if they’ll be on shelves […]

Continue reading

Excel Shortcut – Ctrl+BACKSPACE – Active Cell In View – Video Tutorial

One of the benefits of being Bill Jelen (Mr Excel) is that people send you all kids of tips. This tips was submitted for consideration for Bill Jelen and Szilvia Juhasz’s book 40 Greatest Excel Tips of All Time. Here Is The Scenario You have a ton of data, and you are scrolling all over […]

Continue reading

Dashboards for Excel… it's finally finished!

It’s finally done! That’s right: the entire manuscript is done and in production. It’s going to the printers soon. My publisher hasn’t yet updated the Amazon page, but the planned release date is September 15th! Now, as you might recall, we spun out eight chapters from Dashboards to become Advanced Excel Essentials. So if you already […]

Continue reading

How To Concatenate In Excel – Ampersand Shortcut

Using Ampersand Formula In Excel Excel MVP Jordan Goldmeier notes that although this quick tip if fairly straightforward, a lot of people don’t know it. First off… Define Concatenate In the simplest terms… its slamming the contents of cells together. Like having the first name in one cell and the last name in a second […]

Continue reading

How To Drill Down in Pivot Tables Using Quick Explore

Given a rich data set, making pivot tables and charts is amongst the first tools we use to start analyzing that data. But what normally gives insight is how one drills down into the data: to find out hidden patterns or correlations or check for anomalies. Observing a striking phenomenon and then finding out the […]

Continue reading

Excel Chart Drop Down – Video Tutorial & Sample Download

There are times when there is a need to cut down on the available data to have a focused view or to make a decision. Or, we might want to create a live tool, such as that for a Dashboard. These are mainly achieved through solutions implemented in VBA in Excel. But … VBA is […]

Continue reading

Long time no see

It’s been a long time since you last heard from me. Several things have happened in my life. Well, first, I had to get a job. My consulting business was doing well, and I was actually pretty good at it (at least, I thought so). But circumstances unforeseen changed what I had to do next; […]

Continue reading

Factorials In Excel – How To Calculate

Have you ever tried to form a band but found it difficult to figure out what possible combinations could be made? Well, the example seems a bit stretched. But we do face problems regarding arrangements and combinations in our everyday life. From choosing which friends to go to a concert with (since you were only […]

Continue reading