15.11.13

Excel and Tableau: When a Table is just a Table (and not data)


There's all kinds of 'analysts', but perhaps the most prolific is the Excel Analyst.  That pretty much encomapses everyone who works with computers.  Excel has provided everyone with the ability to conduct a wide range of analysis and reporting for years.  Anyone can make a chart or table in Excel - you don't need an understanding or training in data analysis.  However, things can get confusing when we move to an analysis tool like Tableau.
Part of the problem is that most of us use the terms Table and Data interchangeably (because we can).  Here's the rub:
Tables, charts and maps are presentations of Data.
In relational databases, Data is stored in Tables.
So a Table can present Data or contain it.
The problem occurs when we try to take a presentation Table and use it as Data for analysis to make a presentation Table or Chart.  Confused?  Yeah, don't worry, lot's of people get confused by this.  It's why most Open Data is available as a presentation Table and not Data.



It would be nice if all open data was available to us in a Data format, but often it's not.  In order to connect the data to Tableau (or even create a pivot table in Excel), we have to delete empty rows and columns and arrange fields into the proper orientation.  Tableau provides a great (free!) data reshaping add-in tool for Excel which takes a lot of the pain out of the process.

Think of the Excel table as data that you can update with new data in the future and if your calculations are in Tableau, they will automatically happen, without extra steps or extra opportunities for error.  MANUAL MANIPULATIONS IN EXCEL = INCREASED CHANCE OF ERROR that can be very difficult to discover until it's too late.  Especially VLOOKUP.



Before you can reshape the data you need to remove all the blank rows and make sure your field names are in row 1. Then you place the cursor in the first cell with a numeric value and re-shape.  Your table is now data.   For fields like this date field which are text strings, you can re-code this easily in Tableau.  Don't create any fields with formulas or calculations in Excel first.  Especially not VLOOKUP.

BTW, have I mentioned that I hate VLOOKUP?  Here's why.  It is perhaps the most used and abused formula in Excel's arsenal.  VLOOKUP requires proper sorting in order to return accurate results.  If you don't know what I mean by that, you need to stop using the formula immediately.  When I receive a workbook that has the formula, I have to check to ensure that it is being used correctly before I can use the data because VLOOKUP doesn't readily let you know that it's not returning exact match results.  To learn more about this, I highly recommend this post by Charley Kyd's at ExcelUser.com.





6 comments:

  1. You're not accurately describing VLOOKUP - the lookup list only requires sorting if you want to look up the value in a range.
    If you want to return an exact match from the lookup list, then the lookup list does not need to be sorted.

    ReplyDelete
    Replies
    1. Sorry, unknown.Missed this. I did mean the two options, but didn't explain it very well. Thanks for the catch.

      Delete
  2. I understand your point, but agree with the last comment. The below is from Excel's help menu. Just enter 'false' to search for exact matches.

    If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

    For more information, see Sort data in a range or table.

    If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

    If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

    ReplyDelete
    Replies
    1. Sorry I missed this - this is what happens when you spend so much time fighting spam - you miss real comments. I've since set comments to 'moderate' so shouldn't miss them anymore.

      Yes, thanks - I didn't explain that very well - thanks for the detail. I am happy that I don't have to spend time with VLOOKUP anymore.

      Delete
  3. I have to agree with you on VLOOKUP, I hope that you have found the joy of MATCH/INDEX instead! :) Thank you for this post. As someone who is struggling to learn Tableau because I still think in "Excel", I'd love to see more posts like this today.

    BTW- discovered you from the Tableau Hangout today. Thanks so much for the info!

    ReplyDelete
    Replies
    1. Hi Anna Bella, yes, I did used to use MATCH/INDEX instead. I started making dashboards in Excel in 2005 thanks to Charley Kyd's wonderful e-book. Was invaluable for thinking in "dashboard" instead of "chart" in Excel. I understand your struggle in switching from Excel to Tableau. There will come a day when you realize you automatically go to Tableau because you will have fewer clicks even for the simplest, quick thing. Its the same transition that we used to have with each upgrade version in Excel.

      I will for sure do more posts like this - Excel Analysts are my peeps! Thanks for attending the hangout today, that was a bit awkward and intimidating, but fun. If you need any help - just drop me an e-mail. Thanks again.

      Delete