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 […]

## 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 […]

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 […]

## 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; […]

## 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 […]

## 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. […]

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 […]

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 […]

## 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 […]

## 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 […]

## 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 […]

## 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 […]

## 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 […]