Make smarter decisions with Excel’s tables and lists. Excel is a great place for comparison shopping or deciding which product to buy. Unlike Word or OneNote, Excel has tools to sort, filter and do calculations to help you narrow down many choices to just a few or one.
In this article we’ll show you a sample comparison worksheet and then look at some of the Excel features you can use (or not).
Here’s the sample we’ll use, a fictional list of computer drives. The same principles apply to anything from a house, apartment or car right down to a TV, Internet provider or mobile phone plan.
This list shows us the options with some calculations, highlighting the best value (price per Terabyte before and after shipping/tax), two ways to insert comments and more.
Tip: we’ve got a little bonus for people who read all the way to the end of this article.
Once you’ve used the worksheet to choose a product, add another tab to choose the retailer with the best overall price with shipping and taxes. Excel is often seen and sold as a tool for enormous and complex calculations which overlooks its simpler uses in the home and small business. The mobile versions of Office mean you can hold a worksheet in your hand while shopping.
Here’s some of the Excel features we’ve used to make this list. Some are obvious, others less well known.
If we mention an Excel feature you’d like to know more about (or we didn’t mention) – let us know and we’ll try to write about it in a future issue of Office for Mere Mortals.
One document, visible everywhere
The developments in Office over the last few years mean you can take your shopping analysis with you anywhere.
Save the Excel worksheet to OneDrive or Dropbox, then open it on a tablet or smartphone when you’re in a store. Change the prices, add another row or edit other details to see your revised choices. The important Sort and Filter options are there, just like in Excel Windows/Mac.
Here’s the same worksheet, on an Android phone open with Excel Mobile.
Microsoft’s hype would like you to think about making the entire worksheet on a tiny screen. Out here in the real world, it’s easier to make the main worksheet on a Windows or Mac computer then view or make smaller changes on a smartphone.
Making the list then using it
Firstly, we’ll look at some Excel features for making the comparison list and then how to view and manage the list from wherever you are.
Making a comparison list
Tables
Start by adding some column headings and maybe a line or two of detail. You can add more columns and rows later.
Then convert it into an Excel Table
Why use an Excel Table?
Excel Tables do a lot of the formatting for you and setup the sorting/filtering options that will be useful later.
More important, Tables automatically use column names as cell references. That makes it easier to understand a formula and lets you move/copy columns without worrying about relative cell references.
To make a table select all the columns and rows and choose Format as Table:
The default table formatting has most of what you need; header row, banded rows (easier to read) and the filter buttons.
From Table Tools | Design you’ll may want to select ‘First Column’ to highlight the product name.
Though we prefer to format the first column with bold text, the choice is yours.
Numbers, Text, Links, Images
You can add columns for any specification or detail of each product that you wish. They don’t all have to be numbers, they can be comments, product codes, links to the purchase page on different sites even images.
Sometimes photos are a useful reminder, for example buying a car:
Auto Fill is your friend
Consistency in Excel lists or any database is important which is why Excel’s AutoFill option is useful. When you start typing, it will offer any similar option already in that column.
Later, when you’re sorting and filtering your list, this consistency is invaluable.
In this example we’ve used two names for the same brand; Western Digital and WD. When we type ‘W’ in the third row, Excel suggests the name of the previous row which saves typing and ensures consistency.
Force Text Formatting
Some information is text but Excel thinks it a number. That changes the formatting and sorting behaviors. It happens with product codes aka SKU’s or UPN codes which are useful for ensuring you’re comparing exactly the same product from different retailers.
Select the column and choose Home | Number | Text.
Tip: left formatting the column helps as a visual clue that this is text, not numbers.
Smaller and larger fonts
Some information is more important than other details, don’t be afraid to adjust the column text sizes accordingly. The price and capacity are more important than, say, your notes.
This can also help fit all the columns into a single screen view.
Mismatched Information
Ideally, each column has the same information so you can sort and filter it, but sometimes that’s not possible.
Here’s an example with Solid State Drives and older physical Hard Drives. Each uses a different general performance measure.
Notes and Comments
There are two ways to add text notes or comments to your comparison list.
Notes column, usually the last column.
Use the Home | Alignment | Wrap Text so the text appears on the screen instead of disappearing off to the right. We usually make the font size smaller, so more is visible on the screen.
Comments. These are in-cell remarks originally intended for worksheets being used by many people but are also available to individuals. We use them to remind ourselves of how the sheet is setup or for details that need more explanation or checking.
Look for the red triangle in the top right of a cell. The Comment will appear when you hover over that cell.
Insert Comment is on the right-click menu for any cell or Review | New Comment.
Highlighting
Just like in Word documents, you can use Highlighting (Fill Color in Excel) to mark cells that need more work or checking. Perhaps use the yellow Fill Color with a comment to explain.
Calculations
Of course, where Excel shines is in calculations. In this case the formula is simple division; the cost of each drive divided by the capacity. The price per Terabyte is an indication of how much you get for your dollars.
Our example has two prices for each drive; the drive alone and with tax and shipping.
If you’ve used an Excel Table, the formula will automatically use named ranges instead of cell references:
=[@[Price with tax and shipping]]/[@[Capacity TB]]
Instead of the mysterious:
=L2/B2
Viewing your shopping list
Now you have a shopping list, what can you do with it? As your list of options gets longer, Excel makes it easier to narrow them down to the ones that are relevant to you.
Conditional Formatting
Excel’s conditional formatting lets you quickly see the extreme values – best/worst, cheapest/expensive etc.
Select the data in a column then Home | Conditional Formatting then whatever plan suits you. We use the color scales as a simple way to mark the cheapest (Green background) and most expensive.
Conditional formatting has other uses as this selection of columns demonstrates:
The left column simply highlights the drive/s with the largest capacity. ‘Price per TB’ on right uses the Data Bars to show relative pricing instead of the Color Scale background fill. ‘In Stock’ highlights the items in stock or not.
Filtering
Those little arrows/wedges on each column header let you narrow down the choices in many ways.
Some filters are simple selections, for example deciding that some brands aren’t acceptable (NZ and Trevor). Click on the Filter arrow and de-select those options.
Sorting
Re-ordering to put the cheapest at the top is on the same button as filtering.
Freeze Panes
Freeze Panes keeps the right columns and top rows visible as references as you scroll around the rest of the worksheet. Click on the top left cell ‘corner’ and choose View | Freeze Panes | Freeze Panes.
Bonus
Check out the example worksheet for yourself. Download here and open in Excel. It’s an standard .xlsx Excel file with no macros. The usual statements apply in these legalistic times <sigh>. Naturally we’ve checked it for viruses but you should do your own checks of ANY downloaded file, regardless of the source. Intended as an example of Excel features only, the names, prices and other details are made up. Use at your own risk, don’t talk to strangers, don’t run with scissors, blah, blah blah.
