Clean up Incorrectly Formatted Phone Numbers using Excel
In our Utopian imaginations all the data would have been standardized and shareable across systems and people. But alas, the reality is totally different. We seldom get data in the format / way we desire it to be. In other words, the ingredients are all there, but for us to prepare the dinner, you must […]
Handling spelling mistakes while searching your data using excel
Spelling mistakes are a thing of day to day carporate life. Most of the data in spreadsheets is entered by people and hence prone to having spelling mistakes or alternate spellings. For eg. a person named John could have been spelled as Jon. And when John calls you back to confirm his reservation and you […]
SQL Queries from Excel
Often when you are working on 2 incompatible systems and try to import data from one to another, excel can be a handy tool. I have used excel plenty of times to generate SQL insert / update statements which I could later execute on the database console. Here is a tutorial if you ever have […]
Visualization Challenge – How to show market share changes?
One of our readers Jennifer mailed me with this excel charting problem she is having. I thought why not ask other readers for their advice on this. So here it goes: The Charting Challenge – Visualizing Changes in Market Share Here is an excerpt from her email: The gist of my problem is how to […]
Calculating Paydays in a calendar year using Excel
In India salary is usually paid on the last working day of a month – the payday. It is slightly different in countries where payrolls are processed every 2 weeks. For eg. in US most companies pay salary on every 2nd Friday / Thursday. We can calculate the paydays / payroll periods in excel with […]
Tracking your stock portfolio using Google Docs
By integrating services like Google Finance, Search in to spreadsheet functions, google docs has paved the way for endless possibilities. Google spreadsheet function GoogleFinance() can be used to build stock portfolio tracker sheet that can fetch historical stock quotes to tell how your money is doing. Click here to see the stock portfolio tracker sheet […]
Creating KPI Dashboards in Microsoft Excel [Part 2 or 6] – Adding One Click Sort
Creating KPI Dashboards in Microsoft Excel is a series of 6 posts by Robert from Munich, Germany. This 6 Part Tutorial on KPI Dashboards Teaches YOU: Creating a Scrollable List View in Dashboard Add Ability to Sort on Any KPI to the Dashboard Highlight KPIs Based on Percentile Add Microcharts to KPI Dashboards Compare 2 […]