Learn to use Excel Tables and Filters to Create Dynamic Lists such as References, Case Studies, and More.
Matching the best references or case studies to a specific proposal can be challenging, especially when there are many to choose from and lots of variables to consider. Should you match the industry or location? Is company size relevant or is the proposed solution more important? Or is a combination of all the winning strategy?Microsoft Excel is the perfect tool to help you apply some method to the madness. Learn how to create an Excel table to organize references along with the criteria and functions to make the selection process quick and easy.Gathering the Data
At the simplest level, you will want to create an Excel Spreadsheet with a row for every reference and a column for the reference content as well as for other important information and every criterion that you may want to use to guide the selection. In this (made up) example each row contains a column for country, industry, implemented solutions, and the reference content itself.
Excel Tables
After you have assembled your data into the appropriate rows and columns on your spreadsheet, it is time to apply a little Excel magic.To the References spreadsheet, we want to first add one additional column that will help us keep track of our reference selections. Select column ‘A’, right click and select ‘Insert’ from the popup menu. If you haven’t already done so, add a row at the top and enter headings for each column:

You will be prompted to confirm the range for the table. Nothing to do here if you have selected all the correct rows and tables. But be sure to check ‘My table has headers.’

Fun with Filters
The advantage of turning your spreadsheet into a table, besides looking very nice, is that some very cool features are automatically applied. One you will notice right away is the appearance of filters on all the columns. These appear as small down arrows in the heading row:




This will produce a table showing only the five selected rows that I have identified as the best references for this proposal.

Adding the Selected References to the Proposal
In our References table, the actual reference content is stored as a cell value in each row. Although Excel is much more limited in formatting capabilities than Microsoft Word, it does support some basic direct formatting inside a cell. In this case, we are using bold and italics in the reference content.If the content sufficiently simple and is formatted properly in Excel, bringing the references into the final Word proposal is as simple as copying the reference content column and pasting it into the Word document.When pasting the content into Word you have several choices. You can select one of the paste options on the Ribbon. Hover over the item to see how the table will appear. (You can also try the additional options in Paste Special.)If you paste it in as a table (recommended), you can change the formatting as needed directly in Microsoft Word.
If the content of the references is too complex to be stored in a single cell in Excel, you can store a link or instruction to find the reference content to be used.
Tips & Shortcuts
Apply filter on a single value: Place your cursor inside a cell with the value on which you want to filter. Click the ‘menu’ key if your keyboard has one (a little box with lines on the right between Alt and Ctrl). If it doesn’t, right click to show the popup menu. Type ‘EV’ or click on ‘Filter by Selected Cell’s Value.’ The Filter will be applied without unchecking ‘All’ first. If you need to filter on MORE than one value, you will need to use the filter dropdown instead.