One of Katie’s tips was to use conditional formatting to highlight  your duplicates, then sort based on the formatting color. In case you didn’t know, sorting by cell color is another fantastic tip and trick in Excel!

You can also use conditional formatting to add an icon to your values, which can be formatted in many ways. As with any of these tips and tricks, the most important factor in determining what to use is deciding what you want to show with your data. Are you trying to highlight proposals of a specific value? Are you wanting to compare one value to another? There are so many possibilities and it’s easier than you think!

Filters

A fast and easy way to narrow down a spreadsheet is to use filters. When you apply a filter to your spreadsheet, you can select the data in any column and filter out the rest. For instance, let's say you have a giant spreadsheet of all donors and you want to filter down to only those assigned to a certain fundraiser – click filters and use your fundraiser name column to select the name of the one you are wanting to focus. Perhaps you want to find only prospects living in a specific state – you can do that by using your filters.

Filters are a fast and easy way to break down lists – but it can lead to issues if you forget which columns are currently filtering or if you want to start copy/pasting blocks of data. If you are wanting to narrow down a list to work from, or share with development, you may want to turn your spreadsheet straight into a pivot table.

For many projects, simply filtering the data down will give you a quick idea of what to do next. Another tip: When you apply a filter, look to the bottom left corner of the spreadsheet, and it will give you a count of how many rows of data remain on the filtered list versus how many are in the entire spreadsheet.   

    XLOOKUP

Do you have a perfect database with reporting that meets all your needs? (I’ll wait while you grab a tissue to dry your eyes from laughter or sorrow.) Don’t worry, you are in good company. If you are trying to merge data from different spreadsheets using a unique column they all have in common (e.g., ID number), VLOOKUP can help you locate data in a vertical column, HLOOKUP can help you locate data in a horizontal row, while XLOOKUP can find the value anywhere in the spreadsheet. XLOOKUP is the newer, better, search function that will allow you to search for a value anywhere in the spreadsheet.

Anyone who has used VLOOKUP knows that it will often return an error if the data isn’t sorted in the right way or if the return value isn’t located in the right place in the spreadsheet. XLOOKUP allows you to return data that is to the right or left of your search data and will search both horizontally and vertically, essentially combining HLOOKUP and VLOOKUP and then making it even better. If you don’t know how to do any of these, just learn XLOOKUP and skip the others.

XLOOKUP can help you create a single report when you are trying to merge several different exports of data … and/or when you need to fill in a column that gets added to a report after you’ve already formatted the first report and your boss asks you why you didn’t XYZ in your report. All you need is that common unique identifier, like their ID number, and you can use that factor to take data from one list and insert it into another.

In one example from the webinar, Katie had a list of constituent names and IDs in one spreadsheet and a list of IDs with a ton of data in the other spreadsheet. She showed how to use XLOOKUP to match the ID numbers in the spreadsheets and bring the constituents names into the larger data spreadsheet.

Pivot Tables

We can spend an entire webinar on pivot tables (more on that soon!) which are my personal favorite tool. Pivot tables take your spreadsheet of data and allow you to break it down in meaningful ways. Dashboards are basically data visualizations and pivot tables and data visualizations from pivot tables. The best part is that they are EASY to do! In the webinar, Katie broke down a portfolio by the prospect capacities, which allows you to quickly visualize not only the number of prospects within each stage, but what stage each prospect is in, based on their capacity level. We were quickly able to see that there was a solicitation coming for 1 prospect with a $5m+ capacity as well as 4 prospects with $2.5m - $5m capacity who are almost at solicitation.

With most versions of Excel, you can simply double-click a number in the pivot table and that will generate a list of the data behind those numbers. Want to see the 81 names in Late Cultivation? Simply double-click the 81 in the pivot table and a new sheet will appear showing those names. For more robust pivot capabilities - looking at you KPIs - you may want to turn on Power Pivot in Excel. To do that, simply go to File → Options → Add-ins → Select Power Pivot for Excel → OK

While you’re activating Add-ins, I suggest you go ahead and activate the Power Map, Data Streamer, and Analysis ToolPak.

Even without the complexity of Power Pivot, pivot tables are extremely useful for visualizing data and you can add several pivot tables to a single sheet.

Once you have pivot tables, you can take it a step further by adding pivot charts with filters and sliders - which will quickly adjust all the pivot charts according to specific criteria.

For instance, if you've pulled out all development portfolios with prospect status, you may want to add graphs to show how many of their prospects are within each stage to determine pipeline flow. Another graph to show open proposals. Perhaps another one that shows proposals that have closed in the past 30 days (celebrate successes!) Then you can create a slider which will allow you to select an individual development officer, which will drill all of your charts down to only their data. That way, with one report you have the option to view everything in the pipeline, then drill in to each individual development officer, or focus on development teams, all without leaving your single spreadsheet in Excel. 

We are working toward getting another webinar on the schedule to address your pivot table questions and – as always – please reach out and let us know what you would like to learn more about. We are here to help you excel at all that you do! 

PS: There were many instructive visuals included in the making of this blog, but the size limit to post required their removal. Thank you for reading!