Three rules when using Excel for Data Analytics.
Excel is the father of small company Data Analytics. Actually, it’s the grandson (Visicalc then Lotus 1-2-3), but Excel made us all learn about the power of data analytics. So how can you use Excel to enhance data analytics capabilities today?
Here are three important rules:
· Build Models as Modules
· Keep fixed numbers out of formulas
· Create graphs to explain
Complex data analytics is best with database-driven processes, but if you follow these rules, you can greatly enhance your ability to use Excel to “use and see” your data.
Do you need an airplane or a jet?
The analogy I use for Excel vs. databases is that Excel is like a small airplane and databases are like jets. Small airplanes are less expensive, allow tremendous flexibility and the ability to fly with less concern about where you will end up, because you can land nearly anywhere.
Flying jets are more expensive and require more planning and skill. But while small airplanes may be relatively inexpensive, easy to fly and flexible, they cannot carry large loads, are less reliable and much slower.
So if you want or need to use Excel as a data analytics tool, it helps to enhance its ability to carry larger loads (large datasets) and deliver effective analysis. This can be accomplished by following these simple rules:
Build Modules
Build your analytics using Workbooks. Each sheet should have its own data and logic which solves a distinct (part of the) problem. You can then tie sheets together to solve larger problems. For example, you might have inventory analytics on one sheet and cash flow on another. Inventory can then feed data to cash flow along with vendor terms, forecasting etc. in other models. Each model/sheet can be validated separately to ensure an accurate result. Separate sheets also allows updates or changes (data or formulas) separate from other sheets – so changes are faster, easier and accurate.
Keep fixed numbers out of Formulas
NEVER embed an integer into a formula. When you put a fixed number in the formula, you limit flexibility and greatly reduce your ability to trouble shoot calculations. It is also important to build small, incremental formulas so analysts can quickly understand the logic and verify calculations. Ensuring data integrity – and the ability to easily test conclusions – is paramount to having a useful data system.
Create Graphs to Explain
The visualization of data can be as powerful as the data itself. Visualization allows the user to quickly find and interpret highly complex analytics. But don’t underestimate this skill, visualization is art and science.
Models can be organized into four categories based on the goals: Comparison, Distribution, Relationships or Composition. For example, relating historical data to current data is best in a Comparative model using a trend timeline. As crucial as accuracy is to data analytics, interpretation and presentation of the data itself is equally important.
Small airplanes don’t get you to Hawaii
While these rules can greatly enhance your ability to use Excel as a data analytics tool, as your company grows and becomes more complex, data becomes too big and too valuable to be left to a tool as unstructured as Excel. When you have a clear strategic path and the complexity of your organization requires greater reliance on data analytics, it may be time to consider adding database tools to your capabilities.