Excel Tutorial and Tips for Spreadsheet Analysts

Category Archives for Excel Tips & Tricks

How To Make Radio Buttons Larger With Excel VBA

Microsoft Excel allows you to create the form with radio buttons, text boxes, check boxes and everything needs to complete the form. Here’s the problem though, form elements in Excel are small and you can’t make them larger. Even if you try to make them larger you are not lucky enough as it does not […]

Continue reading

Change Text Color & Font In Excel – “How To” Tutorial

Have you ever thought to change text color and font in Excel? Till now, we are only aware of how to change color and font for the entire cell contents. Yes, right? But, sometimes you would be needed to change text color and font. I mean changing the color and text to little piece of […]

Continue reading

Query Active Worksheet – Excel Power Query Tips

Let’s say we just got handed a report in Excel and have been asked to calculate various things from it. And the report looks something like the image below. Now, there are two ways to go about it: Use VLOOKUP, INDEX, MATCH and IF statements to calculate all the statistics we need. Convert the sheet […]

Continue reading

Sum Multiple Columns With Excel Macro – Excel VBA Tips

In high-pressured meetings with Chief Executives, no one would want to be seen as clumsy or slow. And we, as data analysts, are assumed to be fast with navigating spreadsheets or calculating various stats. It is certainly not a good choice to let anxiety take the best of us in front if the C-suite. But, […]

Continue reading

Using Named Formulas In Excel – Like Yesterday()

What if there is a way to define new formulas in Excel without using VBA? One would be very skeptical of any such claims. But, believe it or not, it is true. Think about how easy such a technique would make your life. Not only would it allow you save a lot of time, the […]

Continue reading

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
>