Excel trick: Search for multiple keywords from a list
Recently I was given a large spreadsheet with people’s expertise written in long phrases, and I had to identify those people who have AI-related expertise. The issue...
Category
tutorial
Excel Trick: Import Data From a Picture!
Have you ever looked at a table in a PDF file (or a picture) and wanted to import it into Excel? This video tells you how! As shown in the short video, this function is not...
Clustered bar chart and stacked barcharts, switch rows/columns, overlap and customize shapes
Bar charts are one of the most frequently used charts. To use them effectively to generate on-the-point graphs that deliver exactly the right message, however, requires some...
How to draw ranking data properly
A slight problem with drawing data on ranking is how we normally think about ranking. We normally associate “#1” as the best, and “#10” as worse...
Simulation: Is demand equal to sales? (Part II, and why is there a Loch Ness Monster?)
In an earlier post, we discussed the difference between demand and sales using a simulation exercise. Now the question is: How can we visualize the difference? To start off,...
Simulation: It all starts with a coin toss
There are many sophisticated tools dedicated to Monte Carlo simulation, but Excel is powerful enough to handle a variety of simulation needs. Here are the basic steps of...
Two-way Data Table in Excel is a great tool for sensitivity analysis
In an earlier post, I have introduced Data Table as a “secret weapon” to conduct sensitivity analysis. Now let’s go one step further and look at a more...
Filter Data Right in your Pivot Table Fields!
One of the ‘hidden treasure’ in Pivot Table a lot of people do not know about is that you can filter data right in your Pivot Table Fields. This is extremely...
Sensitivity Study (or Scenarios) with Data Table
In managerial decision making, many time we need more than just an optimal decision. Often we would like to know how my results change if I change my decision in a certain...
PivotTable: Calculated Fields
Sometime, after you have made a PivotTable you realize you might want to do some further calculations based on the summary results. For example, After getting the total...