Anywhere online (preferably Ireland) I can get some Excel tuition?

seachto7

Hi all,

Just wondering if anyone might know of any place I could get some tuition in a few Excel tasks?

It involves pivot tables and about three or four tasks I would need help with, all of which involve creating pivot tables and picking out some KPIs and trends.

It's not for work purposes, just personal purposes, but will come in handy for work purposes if I am able to do the tasks.

namenotavailablE

If you don't need a person to sit beside you, YouTube can be an excellent resource for this type of thing- check the ExcelIsFun channel (Mike Girvin). He provides downloadable files and detailed explanations of what the task requires.
Pivot tables and dashboards are well covered by him and many many others.

Taltos

You can always use this forum too for pointers when you get stuck.

seachto7

Ok, I didn't know that. (that I could use the forum). I may as well post what I'm trying to do then.

Generating the pivot table is where I am getting stuck. I can get half ways there, but it's not quite right.

These are questions from a sample task I was asked to do a while back, and I had difficulty getting the pivot table correct.

I left it alone for a long while, and am coming back to it now, as I think getting the hang of pivot table is no bad thing.

I have the excel file as well, but won't post it here unless I am allowed to.

What I was on the hunt for was someone to say: "ok, this is how I generated the excel pivot table", e.g., with bullet points, then I would go away and mess around with it and do it from scratch.

Question they wanted me to answer at the time would have been:

We would like you to analyse this data and perform a SWOT analysis of the PPC performance, including but not limited to the following points:

• Present an overview of the key metrics which you find essential to monitor performance
• What is your process around reporting on these key metrics?
• Summary of current performance
• Summary of trending performance
• Analysis of key drivers of growth including influence of traffic mix in past and future performance
• Conversion trends, and their impact on recommendations for future spend

Taltos

Not sure what exactly you are trying to get to but building a pivot while a bit daunting at first goes get easier with time.
For me - on excel2010 I still change my pivot display to classic but that's just how I roll.

I think you can attach a file - maybe just a sample so you don't share private data - at least I think I have seen other posters do that in the past here.

Taltos

Just while you check that file our here is what I normally do.

1. Select the full range of data I want to build my pivot on. If the data set is going to grow I just select the Columns eg From Col A through to the last one, not the rows, this way if you add on more rows of data all you have to do is refresh your pivot, great if you just want to use the pivot as a template pumping new data in each week.
2. On the Toolbar select INSERT > PIVOTTABLE
3. You can adjust the range here and you can also select to create the pivot on any other sheet or as a new sheet.
4. In 2010 at this point I right click on my pivot and goto PivotTable Options > Display > Classic PivotTable Layout
5. Then just add in the following as needed - you don't need them all
> Filters
> Column Labels
> Row Labels
> Values - and you can adjust these to show counts, etc

seachto7

I don't have a problem sharing the data. It's old at this stage.

I'll go through your post there and give it a crack.

The file seems to be too big to upload, so I'll try the URL option.

https://www.dropbox.com/s/o8vl9p85yh8dxy0/Task%20Dec%202012.xlsx?dl=0

Taltos

OK - pulling it down now.
Any idea of what columns / rows you want or how you want to see it.

eg right now this is what I have
Ad Group|Google Display Network|Google search: Other|Google search: Top|Search partners: Other|Search partners: Top| Grand Total
Bilbao Airport - [Cheap Rental Cars] [paused]|||1||1
Bilbao - [Cheapest Car Rentals] [paused]|||1||1
Denver Airport - [Hire Car] ||1||||1
etc

https://www.dropbox.com/s/wt1vvdciy8zzfkt/Task%20Dec%202012_updated.xlsx?dl=0

seachto7

Thanks for that. I'll be online again in the morning, and will have a proper go at this and report back. Owe you a virtual beer.

Taltos

Don't worry about it, pivots are one of those things that loads of folk have issues with until the day it clicks.
Unfortunately they don't do everything but they are fairly handy, especially when used with other functions.

Jeju

Came across this earlier.

http://www.groupon.ie/deals/cork/excel-with-business-292-395/47248811

seachto7

Cheers, that might be what I need. To the others that posted with tips, I have set aside tomorrow morning to go through them.

seachto7

Taltos wrote: »

Just while you check that file our here is what I normally do.

1. Select the full range of data I want to build my pivot on. If the data set is going to grow I just select the Columns eg From Col A through to the last one, not the rows, this way if you add on more rows of data all you have to do is refresh your pivot, great if you just want to use the pivot as a template pumping new data in each week.

Ok, I did this, I think I did it correctly. I just selected the columns with data

2. On the Toolbar select INSERT > PIVOTTABLE

Ok, did this

3. You can adjust the range here and you can also select to create the pivot on any other sheet or as a new sheet.

Not too sure what to do here, guessing my way through this bit

4. In 2010 at this point I right click on my pivot and goto PivotTable Options > Display > Classic PivotTable Layout

Can't seem to find this option?

5. Then just add in the following as needed - you don't need them all
> Filters
> Column Labels
> Row Labels
> Values - and you can adjust these to show counts, etc

I can't get it to remotely look like what you posted in the dropbox option.

Taltos

Send on what you have again and how you got it looking.
I will send back the same file with a step by step including screenshots for you to practise with.

seachto7

Ok, I'll send on what I am doing step by step. I've just put this off for the last month and can do so no longer

Taltos

The thing with pivots is once you get it you get it, but they are tricky at first.

seachto7

ok, this is what I am doing

1) Selecting all the columns with data

[IMG][/img]

2) Pretty sure I'm not choosing the right things here



3) This is where I get stuck. Not too sure what I should be doing here



4)

This is what you would have uploaded to Dropbox before. These would be the columns

seachto7

I downloaded the Excel sheet you edited from Dropbox, so might try and work my way back from what you did.

Edit: Ok, I worked backwards and still can't get it to look like what you did.

I think I also have a problem getting the "average of average position" in the value box/section.

Taltos

Perfect - just missing one or two steps. Between 3 and 4...

Taltos

In 3

when you have the pivot shell or outline - before you add anything to it (or if you have right now).
Just right click on it.

Select > PivotTable Options
Here you get a popup.

You can name it but right now goto
> Display

Uncheck - "Show expand/collapse buttons"
CHECK - Classic PivotTable layout...

Later - if you want each row to repeat the field above you do this on a column by column basis, again using right click & "Field Settings" & under "Layout & Print" Select "Repeat Item Labels" - but you only need that if rows are repeated