Excel: The Underused Manufacturing Tool
Microsoft Excel is an incredibly powerful tool that remains underused by many businesses. It has hundreds of specialized functions that can give your data depth and make it work for you. The great thing about Excel is that even if you aren’t a “numbers person,” you can use it to help you see trends and patterns in data. The examples in this article touch on just a few of the many useful functions most people don’t ever use.
The key to becoming a Microsoft Excel guru lies in two places: your ability to ask the right questions and your ability to get Excel to tell you the answers in a way that’s useful.
Asking the right questions usually isn’t difficult. It involves asking, “What do I need to know about the data in this spreadsheet?” For example, “How many of my machines are underproducing? Which ones?” or “How many units did each of my salespeople sell in each region for each quarter of the year?”
The second (and tougher) part is knowing the right tools to use. A good first step is to write down in plain English what you want Excel to do for you. For instance, if you want to know how many of your salespeople sold fewer than 1,000 units this quarter, you might write down, “Count the number of cells in the ‘units sold’ column where the value of the cell is less than 1,000.” Using this strategy will help you clarify exactly what kind of results you are looking for. From here you will be able to determine what function to use.
So what if you actually need to count the number of cells in a row or column that satisfies certain criteria? Excel has a function designed to do just that. It’s called COUNTIF. Simply enter into a cell =COUNTIF(range,critera), where the parameter “range” is an array (a selection of more than one cell) of cells, and “criteria” is the criteria against which each cell in the array will be evaluated. In the earlier example it would be “1,000”. This formula returns the number of cells in the specified array that are greater than 1,000. Like this formula? SUMIF and AVERAGEIF do the same thing, except that SUMIF adds only cells that meet certain criteria, and AVERAGEIF averages cells that meet certain criteria.
Another powerful feature of Excel is Conditional Formatting. The specific process varies from version to version, but the basic premise is this: You can automatically change the formatting (font, style, color, cell color, etc.) based on certain criteria. To do this, select a range of cells and click “Conditional Formatting” (on the ribbon in the Home tab in 2007; in the Format menu in 2003) and select your criteria. For example, you could highlight in red (or blue or green) all the machines producing less than a certain number of widgets in a given time period.
One of the most exciting tools that Excel has to offer is called a PivotTable. The reason this particular tool is so amazing is that it can provide a lot of actionable data with just a few clicks. A pivot-table essentially is a table that has a row, a column, and cells in the table created by the row and column. All the data is populated from an existing spreadsheet. For example, the row might be “Region” and the “Column” might be “Salesperson” and each cell for every corresponding salesperson and region would be the number of units sold by each salesperson in each region. This particular PivotTable tells you at a glance your top seller, your top seller by region, your top selling regions, and your total units sold in each region.
Obviously the features listed here are a miniscule fraction of what Excel has to offer. Where do enterprising people looking to get the most out of their Excel experience go? An Internet search engine, of course. There are hundreds of sources of free tips, lessons, and other aids written in plain English that can help you find functions that will make your spreadsheet work for you.
