This chapter is from the book
This chapter is from the book
This chapter is from the book
Data Management in Excel
It’s hard to find a tool that, like Excel, combines power, flexibility, and ease of use for some basic tasks when compared to other similar tools. The problem is that Excel training often focuses too much on the tool and leaves out taskspecific aspects.
For example, take chart making. Knowing how to “make charts in Excel” and knowing how to “make charts” are two different creatures. Give a monkey a banana every time it presses F11, and you get a (very low-paid) Excel chart maker (Figure 4.10).
)
Figure 4.10 A default chart when pressing F11.
The same happens with the data. Unlike database applications, Excel does not impose any kind of structure, and because users lack the right training, they believe that this is the natural way to manage their data. Sure, people in IT make data structure a top priority, but they don’t really understand business needs, do they?
Many organizations can gain much if there’s a greater mutual understanding of IT and user roles. Users must obtain a minimum level of literacy with data structures. They must see how structuring the loose spreadsheet environment maximizes the power of functions and formulas that take advantage of that environment (pivot tables and lookup formulas, for example). This simplifies chart making, adds interaction, and reduces updating and maintenance costs. IT personnel and data users may sometimes have a conflicting relationship, but a greater proximity and understanding may help them all realize that users are not always a danger to system security, and IT is not always unaware of business needs.
Organizing the Workbook
The number of worksheets in an Excel file is virtually unlimited, and, surprisingly, we can use all we want without incurring extra costs. Hence, an Excel file that has some level of complexity must be organized in a way that clearly separates the results (charts, tables), intermediate calculations, parameters, and data tables in different, specialized sheets.
Links Outside of Excel
An IT-managed BI system in an Excel-centric organization risks becoming a dual BI system in which users get the data from the formal system, but all the actual analysis is done in Excel. This can quickly get out of control, with isolated file archipelagos in each computer, and impossible-to-reconcile data.
You can’t eradicate Excel as a BI tool unless you uninstall it. The organization should have a better understanding of why users keep using Excel. If the formal BI model can’t address those needs, it should provide direct access to data in a safe and controlled manner, which again requires a closer relationship between users and IT.
The ideal scenario is to create one or more tables that closely match the user’s needs, connected to her workbook and from where she can refresh data.
Formulas
When one of the papers that shaped recent economic policy worldwide4 draws conclusions based on faulty Excel formulas, and when news of millions of dollars being lost due to spreadsheet errors is common, the least we can do is to assume that a formula is a potential threat. With all other things equal, using fewer formulas makes a spreadsheet simpler to maintain, improves performance, and produces fewer errors.
Calculations with a database query are faster and errors are often easier to spot (you get to the needle-in-a-haystack frustration level much faster in Excel than when using database queries). You can connect your workbook to a query in an external database that performs all the calculations before feeding the data into the spreadsheet. And there are many other ways to avoid formulas, such as using pivot tables instead of aggregate formulas or using a data model instead of lookups. Array formulas and calculations in tables are also safer and faster. Finally, named ranges are your friends; use them extensively.
Go to the web page
So, as a mantra, you should think, “Avoid Excel formulas.” This seems to contradict the very nature of the application, but when you avoid formulas, your workbook becomes safer and more solid. Note that the point is not to turn your workbook into a formula-free zone (that’s almost impossible) but to think about better alternatives. Also, you should infer from the techniques suggested above that “avoid formulas” doesn’t equal “hardcode data” (entering a value instead of a formula).
Cycles of Production and Analysis
There is a major difference between business visualization and media infographics.5 Unlike most infographics, which aren’t updated after they’re published, business visualizations usually include a set of representations that remain useful from cycle to cycle and cut across the organization. Charts on market share and growth are updated for each cycle. They are seen at various levels of regional detail and are common to the multiple markets in which the organization operates.
Think of business charts as the three Rs of ecology:
They should be reused across multiple markets.
They should be recycled by updating the data.
Their number should be reduced, making business visualization more cost-effective at multiple levels.
This does not cover all the data visualization needs in an organization, and you may use many charts only once, but try to evaluate whether a chart has the potential to be used more than once. If the answer is “yes,” you should evaluate whether it makes sense to spend extra resources to prepare it for repurposing (by adding interaction or creating a database query, for example).
This is just a small part of the many things that relates to data management in Excel. If it were possible to synthesize this management in a single word, that word would be “structure.” Recent Excel versions have introduced new features that suggest more investment in the data structure (including tables, data models, Power Pivot, slicers, PowerBI, and so on). This, in turn, allows you to manage a growing volume of data more effectively.
- Go to the web page
