Excel Tutorial and Tips for Spreadsheet Analysts

Category Archives for Excel Tips & Tricks

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

Convert Function In Excel – Video Tutorial

Tired of googling how to convert inches into meters or how many liters a gallon equates to? It is high time to stop being overwhelmed whenever you next face a unit conversion! And Excel is here to help. Szilvia Juhasz (aka XSzil) is there to teach us the ins and outs of this ‘where-were-you-all-my-life’ function. […]

Continue reading

Graph A Timeline In Excel – Video Tutorial & Download

In the post-apocalyptic world, can you visualize how much time would you have before the Grim Reaper shows on your doorstep? Well, you don’t have to worry about it now. Also, given that Oz du Soleil is here, you do not have to worry about making timelines. Oz is taking us through an exploratory (and […]

Continue reading

Standard Deviation in Excel – Video Tutorial and Download

This doesn’t have to be difficult. It doesn’t have to be hard. No need to stick your head under the covers. There is no boogie-man coming to get you. Doing “mathy” type things in Excel can actually go a long way in helping you run your business or team. Quit Reacting So Much It is […]

Continue reading

Solver Samples already included in Excel – Excel Tips

Most of you must be familiar with Solver. It’s a built-in tool in Excel which helps you solve optimization problems like maximization of profit, minimizing cost of transportation, minimum cost staff scheduling etc. These problems are so widely encountered that people have made and taught specific samples to use when solving a specific type of […]

Continue reading

Finding VBA Definitions – Excel Tips

After having written thousands of lines of codes over multiple sheets or modules, it is already hard enough to remember which sub-procedure does what. On top of that, what if you want to use a particular sub-procedure but you don’t remember where it is defined? Surprisingly, you DO NOT have to Ctrl+F it in every […]

Continue reading

Cleaning Name Suffixes with Array Formulas

Have you ever wondered how to sort lists using 2nd word in each cell? Sounds a bit tricky, doesn’t it? But don’t you worry, Oz du Soleil is here to help. He uses a neat trick to sort out full names by the last names. The approach is interesting and can be modified to achieve […]

Continue reading

Power Query – Splitting Names From Suffixes

Splitting Names From Suffixes Data cleansing and prep can come to you in any manner and that’s why I often compare it to bull-riding: you’ve got to get on and deal with whatever happens. In this video, we need to split the names apart from suffixes like: III, DDS, EdD, etc. This happens when you need […]

Continue reading

Excel Cannot Insert Object ActiveX Error and When to Use ActiveX – Excel Best Practices

Not long ago, Microsoft had an update to Excel that broke a lot of ActiveX Control Boxes.  If you are having this problem, Microsoft has a fix and a support article. Excel Cannot Insert Object ActiveX – Microsoft Support Document: http://support.microsoft.com/en-us/kb/3025036 Several solutions are offered in the article.  Te main solution is to ensure all […]

Continue reading

Radio Buttons for Conditional Formatting – Video & Download

Oz du Soleil experiments with how to visualize music in Excel.  To do this, he takes the song “Pressman” by Primus.  Oz is a bit of a bass player, so his choice of Primus is…  well… not entirely unexpected. Shocker. But what is a bit surprising… or novel… is how he uses Excel in a […]

Continue reading

CONVERT function in Excel – Convert Fahrenheit to Celsius and more – Excel Tips

Are you converting your measurements like a chump? This happens all the time. You get a data dump from your main office in London. Can’t wait to start the analysis. Only to find out that the data uses British measurements. What is a stone anyways? And if you are a chump… like most of us […]

Continue reading
>