All articles with 'Analytics' Tag
Anyone working on the data using excel will know the importance of lookup formulas. They are vital for making almost any spreadsheet or dashboard. That is why when my friend John Franco, who maintains Excel-Spreadsheet-Authors.com, wrote to me about his new book Excel lookup toolbox I was truly excited. In this post I am going to share my review of this product.Continue »
Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in excel 2003) is a very powerful and supercool feature that you must learn if your work involves handling tables of data.
What is an excel table?
Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”Continue »
Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data. In plain English, it means, you can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.
In this tutorial, we will learn what is a pivot table and how to make a pivot table using excel.Continue »
Here is a quick formula trick you can use to generate sequence numbers that only increment when there is a change. Assuming the sequence of values are in column C from C3, you can write the following formula in B4 onwards (B3 will be 1, wake up…) =IF(C4=C3,B3,B3+1) Now just copy paste the formula over […]Continue »
Here is a fun, simple and different alternative to traditional dashboards. Introducing….
Use Excel Pivot tables to find and extract unique items in your data. This method is very fast and easily scalable.Continue »
Microsoft excel bubble chart based Visualization to understand how various travel sites compete search termsContinue »
Check out user journeys and other cool visualizations in this weeks edition.Continue »
Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it. Here is an excel formula trick to generate timestamps.Continue »
Often when you are writing code or testing an application, it might be helpful to have random phone numbers generated for you. Here is an excel formula that I use: =value(left(substitute(rand(),”0.”,””),10)) try these 2 instead (thanks to John) =RAND()*(9999999999-1000000000)+1000000000 =RANDBETWEEN(1000000000,9999999999) [needs analysis toolpack add-in or excel 2007] Even though it works most of the time […]Continue »
Often I wish Microsoft had spent the effort and time on a data genie (and a set of powerful formulas) that can automate common data cleanup tasks like extracting duplicates, makings lists unique, find missing items, remove spaces etc. Alas, instead they have provided features like clippy which are intrusive to say the least. So […]Continue »
This is a Guest Post by Robert on Visualization Techniques for KPI Dashboards using Excel. This 6 Part Tutorial on Management 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 KPIs in […]Continue »
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 […]Continue »
In shuffling a list of items in excel I have described the technique of using random numbers generated by RAND() to sort a list of items. The technique had one disadvantage though, every time you need to reshuffle the list you have to press F9 to recalculate the rand() and then go to menu > […]Continue »
This is 4th part of Creating Management Dashboards in Microsoft Excel 6 post series by Robert. This 6 Part Tutorial on Management 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 KPIs […]Continue »