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

Kurfurst, Tina tkurfurst at path.org
Thu Nov 7 16:31:24 PST 2013


As long as we're sharing the love and asking questions:

Does anyone know what setting I need to have so that foreign names dumped out of my db into csv/Excel file don't get munched? Such as: "Ministère de la santé Côte d'Ivoire" turning into "Ministère de la santé Côte d'Ivoire"? The names import into the db OK with the diacritical marks from a csv file, but come out mangled. I've been Anglicizing everything I come across, and that's better than re-fixing the names every time they're dumped out, but tedious either way. Any help here would be MUCH appreciated.

From: apra-nw-bounces at mailman2.u.washington.edu [mailto:apra-nw-bounces at mailman2.u.washington.edu] On Behalf Of Kurfurst, Tina
Sent: Thursday, November 07, 2013 4:04 PM
To: Susan Hayes-McQueen; Hanlon, Erin; 'apra-nw at u.washington.edu'
Subject: [APRA-NW] RE: Virtual Roundtable for November: Excel tricks we love!

=subtotal(9,x#:x#)

I use this little formula all the time when column filtering to get a subtotal number without having to use the subtotal feature.

Example: Filter on State=WA, total gift count >=1, total gifts from WA donors=subtotal(9,x#:x#) beneath the gift total column, where x# is your Column/Row range. You can change your filter criteria (OR, CA, total gift count>=3, etc.) having to re-subtotal because it only counts what is being filtered.

It will also show the count of the filtered records down at the lower left of your screen: 3878 of the 8872 records on my spreadsheet met my filter criteria.

[cid:image004.png at 01CEDBD6.C9DFE160]

From: apra-nw-bounces at mailman2.u.washington.edu<mailto:apra-nw-bounces at mailman2.u.washington.edu> [mailto:apra-nw-bounces at mailman2.u.washington.edu] On Behalf Of Susan Hayes-McQueen
Sent: Thursday, November 07, 2013 3:21 PM
To: Hanlon, Erin; 'apra-nw at u.washington.edu'
Subject: [APRA-NW] RE: Virtual Roundtable for November: Excel tricks we love!

I feel that I must respond and say that Concatenate is one of my favorite functions.




From: apra-nw-bounces at mailman2.u.washington.edu<mailto:apra-nw-bounces at mailman2.u.washington.edu> [mailto:apra-nw-bounces at mailman2.u.washington.edu] On Behalf Of Hanlon, Erin
Sent: Wednesday, November 06, 2013 4:33 PM
To: 'apra-nw at u.washington.edu'
Subject: [APRA-NW] RE: Virtual Roundtable for November: Excel tricks we love!

Love the "scaling" tip, Susan!

I tend to rely on "text-to-columns" and "concatenate" quite a bit when I am dealing with data I didn't pull myself. Text to columns to split information from one cell into multiple cells and concatenate can be used to join cells.

For example, if my excel file has last name, first name and ID number all in one cell, I'd use text to column to get rid of the comma and create columns with first name, last name and ID. You can use concatenate to join first name and last name (or any other cells) together into one column. Very useful!

[cid:image005.jpg at 01CEDBD6.C9DFE160]


[cid:image006.jpg at 01CEDBD6.C9DFE160]

From: apra-nw-bounces at mailman2.u.washington.edu<mailto: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<mailto: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



[cid:image007.jpg at 01CEDBD6.C9DFE160]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20131108/3467bd20/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 5563 bytes
Desc: image004.png
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20131108/3467bd20/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image005.jpg
Type: image/jpeg
Size: 12115 bytes
Desc: image005.jpg
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20131108/3467bd20/attachment.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image006.jpg
Type: image/jpeg
Size: 36239 bytes
Desc: image006.jpg
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20131108/3467bd20/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image007.jpg
Type: image/jpeg
Size: 53140 bytes
Desc: image007.jpg
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20131108/3467bd20/attachment-0002.jpg>


More information about the APRA-NW mailing list