Excel Graph Gurus HELP

Joanne Felci · March 2020

It has been years since I've really used graphs in excel and I'd love some advice from folks - and any training links and things you use

Ryan Hyde‍ has been my Excel Golden God in the past so perhaps he has some info

But I'm sure there are others as well

Here is what I'm trying to do:

Analyze year to year giving by month with different lines/bars/whatever would be best to pull out giving amounts

For example FY18 vs FY19 month by month with different giving levels for each month per each fiscal year

Giving levels are:

50K+

25-50K

10-25K

5-10K

2500-5000

1340-2500

under 1340

Thoughts?!

Hope all of you are staying well, safe and sane

Ryan Hyde · March 2020

I have been summoned

So, are the bars you're looking for the # of people who fall within each giving category per month & fiscal year? Or are they the total amount given?

Joanne Felci · March 2020

The power of the @ I love it!

I'm basing it on gifts themselves instead of individuals as we want to see when/how money is coming in over time.

And I'm thinking there would be at least two separate graphs:

1. money itself

2. number of gifts at a level and time frame

Ryan Hyde · March 2020

Okay, so for the first graph, you'll want to export all of the appropriate gifts from RE, not summaries for donors. Include whatever other info you want for the detail, but make sure you have gift date and amount, obviously.

Then, create two new columns, one called "month" and one called "year".

In the "year" column, just populate it with the formula "=YEAR([gift date cell])"

In the "month" column, populate it with this formula: =TEXT([gift date cell],"MMM")

What that does is return a 3 character abbreviation for the month.

Now that you've got your columns, you can insert a pivot table. Put the Year and Month filters in the Axis Fields box, and put the gift amount in the Values box. And then, you just need to insert a bar graph based on your data. You should have a good starting spot at this point.

edit: I'm still thinking about the other graph.

Joanne Felci · March 2020

Thanks Ryan Hyde‍ !! I will try that and see how that looks to start with

Crystal Bruce · March 2020

I was today years old when I learned we can summon Ryan Hyde‍ for excel questions!

Kylea Johnson · March 2020

My mind was blown by this (FREE) training:

https://www.udemy.com/course/excel-data-visualization-for-business-analysts/

Judy Spigarelli · March 2020

Ryan Hyde‍ swoops in for the rescue! Thanks Ryan, this is an awesome solution I didn't even realize I needed. Joanne Felci‍, great question!

Ryan Hyde · March 2020

Hi Judy! Thanks for summoning me! That reminded me that I never addressed the 2nd graph.

So for the 2nd graph, what I'm thinking is that you need a SUMIFS function to total all of the giving per person per year. Basically, you'd create a new column that says:

=SUMIFS([gift amount range],[year range],[year cell],[constituent ID range],[constituent ID cell])

That will give you a yearly total for every gift a person made in a given year. By itself, this isn't useful, but then you can add another column to identify a giving level. You'd need a whole lot of nested IF statements that would logically read something like "If the total is above X and below Y, then the giving level is LOW, but if it's above Y and below Z, then giving level is MEDIUM, etc. Without knowing all your levels and ranges, I can't write the statement for you, but you can play around with it some.

Anyway, once you have that, you can do a pivot report again and add your new Giving Level data to the columns box. You can put in another graph based on this data to see columns for each level at the same year/month intervals as the first graph.

At least I think it'll work. Without a dataset to play with, it's hard to say for sure. I could be missing something.

Excel Graph Gurus HELP

Comments

Categories