[APRA-NW] RE: Virtual Roundtable for November: Excel tricks we love!

Jon Garrow jonwgarrow at gmail.com
Thu Nov 7 12:59:48 PST 2013


Along those lines, I would add that Excel 2012/13 has awesome conditional formatting tools. With a couple of clicks, you can highlight duplicate cells anywhere in a range. It can also automagically calculate percentiles and color-code the values - and much more!

I most often use conditional formatting to color code things like distance, affinity, and days since last contact for regional prospect lists.

Jon

Jon Garrow
jonwgarrow at gmail.com
www.linkedin.com/in/jongarrow/

On Nov 7, 2013, at 3:29 PM, Jessica S. Balsam <jbalsam at uw.edu> wrote:


> I’ll chime in with one of my very favorite formulas, which relies on the IF function. I use this all the time to compare items in a list to see where the dupes are.

>

> If you have a list of IDs, like this:

> 123457

> 123467

> 123497

> 123458

> 123457

> 123647

> 123467

>

> And you want to de-dupe it quickly and easily, do this:

> 1. Sort the list by the ID field. I’ll assume your ID field is in column A. Now it looks like this:

> 123457

> 123457

> 123458

> 123467

> 123467

> 123497

> 123647

>

> 2. In the first cell of an empty column, enter the formula: =IF((A1=A2),”DUPE”,”OK”)

> 3. Copy the formula so it runs the entire length of your list. Now your new empty column says “DUPE” wherever there’s a duplicate, and “OK” where there’s no dupe.

> 123457

> DUPE

> 123457

> OK

> 123458

> OK

> 123467

> DUPE

> 123467

> OK

> 123497

> OK

> 123647

> OK

>

> If you just wanted to know if there were dupes or not, then you’re done. But if you want to de-dupe your list, then carry on with these steps:

> 4. Highlight the whole column where your formula lives and copy it.

> 5. In the first cell of that same column, you’re going to paste the data back in. But instead of just straight pasting, use “Paste Values” (right-click instead of using Ctrl-V to get this option). Now instead of containing the value “=IF(…” your cells contain the text “DUPE” or “OK” and therefore, you can sort on them.

> 6. Sort on your DUPE/OK column. All the dupes line up at the top. Delete all those rows, and voila! A de-duped list.

>

> You can also use this to compare values in two different columns next to each other. Just change the cell reference accordingly, so it looks something like this:

> =IF((A1=B1),”DUPE”,”OK”)

>

> Happy de-duping,

> Jess

>

>

> Jessica Balsam

> Associate Director, Relationship Management

> University of Washington Advancement

> 206-616-9170

>

>

>

> From: apra-nw-bounces at mailman2.u.washington.edu [mailto:apra-nw-bounces at mailman2.u.washington.edu] On Behalf Of Faulhaber, Christina

> Sent: Wednesday, November 06, 2013 5:02 PM

> To: 'apra-nw at u.washington.edu' (apra-nw at u.washington.edu)

> Subject: [APRA-NW] RE: Virtual Roundtable for November: Excel tricks we love!

>

> <image002.png>Great idea to share these. I like to use the filter feature for when what I am after is difficult to do with my query criteria, such as when I’d have to do a lot of merge queries to get at what I am after, or for trouble shooting groups that have shared data whose relationship is not clear (information from our financial aid office!). The Sort & Filter menu choice (at far right top) has a drop down for filter. After you select the row of cells you want to filter on (first row in my example), select the filter/funnel icon from the Sort & Filter drop down, and each cell in the row you selected is now able to be filtered on, by selecting the little triangle in the upper right of the filter cell. You can combine more than one filter at a time.

> <image003.jpg>

>

> Very handy for large groups, but what I don’t like about the filter is that I don’t know how to get an accurate count of number of rows. The spreadsheet preserves the row numbers (understandably). So I resort to creating a new column next to the one I filtered on and then entering 1,2,3 in the first three cells of the column and dragging that down to get the count for the last row. I’m sure there’s a more elegant way!

>

> Christina

>

>

> From: apra-nw-bounces at mailman2.u.washington.edu [mailto:apra-nw-bounces at mailman2.u.washington.edu] On Behalf Of Susan Hayes-McQueen

> Sent: Wednesday, November 06, 2013 3:57 PM

> To: 'apra-nw at u.washington.edu' (apra-nw at u.washington.edu)

> Subject: [APRA-NW] Virtual Roundtable for November: Excel tricks we love!

>

>

>

> Hi everyone,

>

> Did you know that APRA NW is hosting a webinar this month on XL Tips and Tricks, featuring the very talented duo of Jennifer MacCormack and Amanda Jarman? It’ll be on Wednesday the 20th from 9:30-11, and details will be coming soon on how to join in. I’m very excited to learn how to wow my family and friends just before the holiday season!

>

> In the meantime, I thought we could talk on the APRA NW listserv about our favorite XL tricks. They can be simple or complicated—don’t assume that everyone knows everything you do!

>

> So: What’s your favorite thing in XL that others might not know about?

>

> Here’s mine:

>

> It’s in the Print dialog— and it’s called “Scaling.” Do you ever have a spreadsheet that is just a bit too wide to print on one page and you spend hours on column width and font size until everything is annoyingly screwed up? Well, FUSS NO MORE!!!!!

>

> In the print dialog, the last thing on the left nav is called “scaling” and you can tell your printer to do the work for you! In this case, I’ve selected to print all the columns on one page. You can select it for rows too. It’s lovely and makes your spreadsheet lovelier when printed.

>

>

> -Susan Hayes-McQueen

> Director, Advancement Research and Relationship Management

> University of Washington

>

>

>

> <image005.jpg>

>

> _______________________________________________

> APRA-NW mailing list

> APRA-NW at u.washington.edu

> http://mailman2.u.washington.edu/mailman/listinfo/apra-nw


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20131107/cda92390/attachment.html>


More information about the APRA-NW mailing list