Share what you know with millions of people
Focus is the best place to turn what you know into remarkable content
0
Excel for BI Best Practices: What are your 3 tips for using Excel as a business intelligence tool?
Please list 3 tips that you would like to share with the Focus community on using Microsoft Excel as a business intelligence tool. High quality contributions will be included in an upcoming report on Excel and business intelligence best practices.
----------
Thank you for your contributions. This question is now closed. To download the final report please click here.
Events
- Dos and Don'ts of Small Business Marketing May 29 @ 11 am PT
- Lead Nurturing 202: The Next Generation May 31 @ 11 am PT
- The Tricks to Paid Media June 6 @ 11 am PT
- Display Advertising for Brand Awareness June 20 @ 11 am PT
Related Activity
10 Answers
You've already gotten some great comments on how to make the most of Excel, so I won't repeat those here. Instead, I'd prefer to provide some context to the question being asked. Excel is often called the most widely used BI tool on the planet, and many people *do* use it for doing analysis similar to what can be done with a business intelligence tool. However, this statement is misleading.
A true BI tool can do significantly more than Excel, and often times in a far easier way. Excel is highly manual, it's easy to get lost in the calculations, and the analysis that you do is difficult to update later as data gets updated or when new data sources get added.
There are ways to approximate BI with Excel, such as using the new PowerPivot, which is a huge improvement, as well as some other add-ons here that people have already mentioned.
To give you a sense of how the question is misleading though, let me reframe it in a different way. You might as well ask, "How can a bicycle be used as a car?" You can add a cart on the back, a bigger headlight on the front, a louder horn, and a small engine to help you pedal, but you still don't end up with a car. You end up with a motorized rickshaw.
Many of our customers (I work at the BI company Birst), have used spreadsheets in some way to approximate BI before they switched to a real BI solution. They are sophisticated analysts, who know all of the Excel tricks. But generating a weekly report would still take them 40 hours of work - they would start on Monday to get the Friday afternoon reports done. Once they moved to Birst, those reports were updated automatically, and the only work required was to click on the report to view it. The differences are tremendous.
Many people use Excel to approximate BI because, historically, BI has been too expensive for all but the largest companies to afford. However, new, more modern BI offerings have dramatically lowered the costs and made it affordable to smaller and midsize businesses. As people become more aware of these offerings, hopefully people can move past using a spreadsheet tool as shoestring BI.
Barbara makes a very valid point. With well over 500 million users around the globe, it’s no wonder that spreadsheets are the de-facto Business Intelligence/ Analytics application.
They are easy to use, allowing users to collect data, structure it and perform complex analysis and calculations quickly, whether it’s for accounting, finance, manufacturing, product management, engineering, sales, supply chain, customer service, forecasting or other areas requiring data analysis.
Usually, IT folks are uncomfortable that the myriad of personal spreadsheets across the company are nearly impossible to audit, running the risk of propagating errors that could potentially drive critical decisions the wrong way.
To their credit, it’s disturbing to see how human error impacts the accuracy of spreadsheets. A University of Hawaii study , estimates that between 20% and 40% of all spreadsheets contain errors and that 5% of all calculated cell formulas are wrong.
There is also the dangerous possibility of complex spreadsheets used with fraudulent intent or casually compromising the security of corporate data downloaded to spreadsheets and sent outside the company.
While some IT organizations have attempted to ban the use of spreadsheets others, based on their versatility, have adopted their use and integration into the BI environment.
Having worked on both sides of the fence (IT and Marketing) I’d like to share my experience on how to minimize this common issue. Full article http://blog.strat-wise.com/2010/10/31/spreadsheets-and-bi--allies-or-enemies....
As self-diagnosed Excel geek, I could not resist joining this conversation. Here are my top 3:
1) Dive into Pivot Tables, even if it makes you cringe. Spend 20 minutes on the pivot table tutorials on microsoft.com and you'll be slicing and dicing like your web analytics guru.
2) Name your datasets to make your masterpiece reusable and diagnosable if it breaks.
3) Clean up your data up front using Excel to find the dirt. Otherwise you'll discover the double counting or the missing regions the night before your big presentation and recreate all your analysis. Or worse, the excel geek in the funding allocation meeting will point out that your totals don't really add up to 100% and ask you why.
I will admit I had a hard time limiting my suggestions to just three.
I am not a particular fan of Excel as a BI tool but also acknowledge its still likely the most ubiquitous used BI tool in the business side today. Given that here are 3 tips on using Excel as an adequate BI tool. My tips are making the assumption that no other BI solution is available and Excel as been chosen as the tool of choice.
1. Data: Make sure you are pulling from data sources that have been normalized at least with definition, format, and has been quality checked and/or transformed. Do not use unofficial spreadsheets with data that has not been transformed against some common internal data dictionary. Also, do not depend on manually loading data from the various sources unless you are ready for the nightmare of a lifetime.
2. Pivot Tables: Become somewhat of a "power user" of Excel, particularly Pivot Tables. If you don't use the grander functionality of Excel you are basically just automating basic mathematical functions and likely will not get at the answers you were seeking.
3. Track your sources of data pulled so reports can be recreated. Also, track your sources so you can understand how you arrived at the decisions/recommendations you are making. Defending spreadsheet results without good documentation is futile particularly if someone has asked a similar question and gotten a different conclusion.
In summary, know your data; ask the right questions so the right data can be pulled appropriately and the right data used; use the functionality of Excel appropriately to maximize the analysis.
All of the contributors have said enough for a novice to experiment with Microsoft Excel for business intelligence purposes. My tips (especially for expert users) are:
#1. Download PowerPivot Add-in for Excel 2010 by Microsoft. It is an easy way to import massive data from virtually any source to Excel in seconds. It also allows you to add amazing computational and data analysis features to Microsoft Excel.
#2. Once you have data in your Excel datasheet, play with pivot tables and charts, what-if-analysis tool, slicers, and data analysis expressions (DAX).
#3. Select the right type of chart for your data: line graph, pie chart, stacked bars etc. To add more meanings and life to your charts, use third-party add-ons. You will find a lot of useful and handy freeware. I go to juiceanalytics.com. They have a number of colorful charts (for comparison, trend analysis etc.) that you can use for free.
I would suggest starting by downloading the data to Excel in either spreadsheet or CSV format. If necessary obtain data from different sources like order detail (main sheet), customer or market attributes and product or SKU hierarchies.
1) Cleanse and structure the data by running lookups to bring the necessary customer and product dimensions into the main spreadsheet.
2) Add the necessary columns to calculate additional measures. For example, if the main sheet has price, volume, and cost, create calculated fields for revenue, gross profit and gross profit %.
3) Apply pivot tables and visualization graphs to expose the combination of measures and dimensions that identify both problems and opportunities and their causes.
Finally, take the appropriate corrective actions: train the sales force, increase prices, streamline the innovation process, introduce more differentiated products and fire a few unprofitable customers!
Regards, Bill
Excel is a desktop analysis tool that can be used by an analyst or a manager to summarize data, visualize relationships among variables and forecast trends. The keys to using Excel are:
1) have a good dataset that you know how to manipulate. Are there missing data values? How many rows of data should be in the dataset?
2) be familiar with the Excel data analysis tools, especially pivot tables. What is a pivot table? What does it mean to pivot? What is drill down? What is a pivot chart?
3) know when to use different types of charts. What chart is most appropriate for the analysis? What is a regression line? How do you change titles, data series, chart attributes?
Microsoft Excel is primarily not a BI tool, but more and more with each version he resembles one. In lastest version (MS Excel 2010) there are several nice additions that can make your analysing more exciting:
1. Use Sparklines - and incredible transparent, compact and easy tool for displaying trends
2.Use Slicer - when you have a pivot table and you want to fast analyse it for whole range of some dimension's members, with Slicer you will be able to do it and to fast filter the data. Moreover, with several Slicers on a sheet you can see associations - which data are connected to current selection, and which are not.
3. Use Conditional formating - with proper visual design analyzers will be able to discern 'good' or 'bad' values in seconds. You can conditionally format numbers with association to color, shape, arrows and so on.
I’m always the one at work and in the classes I teach to dive into Excel and look at how to quantify a given market trend, capture data, analyze it and create dashboards from the results. Excel 2010 has some great BI features to consider using if their job requires analysis and simplifying complex relationships in data sets.
While I use many features, here are my favorite three:
1. XML Import for RSS – A very powerful feature that gives you the ability to integrate real-time Web-based data into a spreadsheet for analysis, this feature is extremely useful if you are doing pricing analysis or monitoring several sites at once for numerical data. I am using this in Excel 2010 and it works great. Microsoft introduced this in Excel 2003 and they keep improving the usability and reliability of this feature.
2. Enabling Data Analysis In Excel – This is the Analysis ToolPak, and in Excel 2010, it is delivered as part of the standard application, yet not enabled by default. If you are using 2010, click on Options and then Add-Ins to enable it. This is a very useful set of applications for statistical, financial and engineering functions. I use the ToolPak all the time as it has many of the same statistical functions I use when I teach market research and business strategy courses. Included is a multitude of useful applications from Anova to Z-tests with many useful statistical routines included. It’s a great time-saver for descriptive statistics if you are doing a market research report for example.
3. Using the combination of slicers, PivotTables and PivotCharts in Excel 2010 to create a dashboard - Earlier in my career I ran market research at a computer company and the VP of Marketing wanted a dashboard of upper funnel and lower funnel sales activity. Using an enterprise BI application it took about 6 - 7 days to get one just exactly like he wanted it as the data has to be linked internally. Excel 2010 would have made possible to complete the upper funnel and lower funnel dashboard in a day. Using PivotTable and PivotChart in conjunction with slicers, you can make some impressive dashboards quickly.
Best of luck!
Louis
The problem is not analyzing the data in Excel; most people know how to do that.
The real problem is getting the correct data into Excel.
That's why we just launched XLReport - www.xlreport.com - which makes it easy to connect to your database, create queries and push the data into Excel. It even alos to export to real time data Excel file.
Let me know what you think.
Rudolf F. Vanek
Answer This Question