All articles with 'Analytics' Tag

Product Recommendation – Excel Lookup Toolbox

Published on Nov 5, 2009 in Learn Excel, products
Product Recommendation – Excel Lookup Toolbox

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 »

Want to become a Data God? Learn Excel Data Tables

Published on Sep 10, 2009 in Excel Howtos, Featured, Learn Excel
Want to become a Data God? Learn Excel Data Tables

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 Tutorial : What is a Pivot Table and How to Make one

Published on Aug 19, 2009 in Excel Howtos, Featured, Learn Excel, Pivot Tables & Charts
Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one

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 »

Create a number sequence for each change in a column in excel [Quick Tip]

Published on Jun 29, 2009 in Excel Howtos, Learn Excel
Create a number sequence for each change in a column in excel [Quick Tip]

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 »

Tweetboards – Alternative to traditional management dashboards

Published on May 7, 2009 in Charts and Graphs, Featured

Here is a fun, simple and different alternative to traditional dashboards. Introducing…. tweetboards.

Continue »

One more method to find unique values in excel and you can call me a dork

Published on Feb 3, 2009 in Excel Howtos, Learn Excel
One more method to find unique values in excel and you can call me a dork

Use Excel Pivot tables to find and extract unique items in your data. This method is very fast and easily scalable.

Continue »

Visualizing Search Terms on Travel Sites – Excel Dashboard

Published on Jan 19, 2009 in Charts and Graphs, Learn Excel
Visualizing Search Terms on Travel Sites –  Excel Dashboard

Microsoft excel bubble chart based Visualization to understand how various travel sites compete search terms

Continue »

Featured Visualizations – Jan 09

Published on Jan 10, 2009 in Cool Infographics & Data Visualizations
Featured Visualizations – Jan 09

Check out user journeys and other cool visualizations in this weeks edition.

Continue »

Automatically insert timestamps in excel sheet using formulas

Published on Jan 8, 2009 in Learn Excel
Automatically insert timestamps in excel sheet using formulas

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 »

Generating Random Phone Numbers

Published on Nov 20, 2008 in Analytics, Learn Excel
Generating Random Phone Numbers

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 »

Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]

Published on Nov 6, 2008 in Analytics, Learn Excel
Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]

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 »

KPI Dashboards – Compare 2 Decision Parameters [Part 5 of 6]

Published on Oct 9, 2008 in Analytics, Charts and Graphs, Learn Excel
KPI Dashboards – Compare 2 Decision Parameters [Part 5 of 6]

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 »

Handling spelling mistakes while searching your data using excel

Published on Sep 25, 2008 in Analytics, ideas, Learn Excel
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 […]

Continue »

Sorting a list of items in random order in excel – using formulas

Published on Sep 23, 2008 in Analytics, Learn Excel
Sorting a list of items in random order in excel – using formulas

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 »

Excel KPI Dashboards – Adding Micro Charts [Part 4 of 6]

Published on Sep 10, 2008 in Charts and Graphs, Featured, Learn Excel
Excel KPI Dashboards – Adding Micro Charts [Part 4 of 6]

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 »