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 readingHow 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 readingExcel 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 readingLong 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 readingFactorials 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 readingConvert 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 readingStandard 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 readingSolver 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 readingFinding 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 readingCleaning 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 readingExcel 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 readingRadio 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