Jump to content

Excel People


wykikitoon
 Share

Recommended Posts

7 hours ago, Gemmill said:

Absolutely absurd that there hasn't been a HUGE response to my post above.  Basically if you're doing ANYTHING with data in a spreadsheet, using Power Query will probably earn you at least a day off a month. 

 

Ok I'll bite. How are you using it and what is it doing to help you. I use excel a lot but haven't tried this.

Link to comment
Share on other sites

6 minutes ago, Rayvin said:

 

Ok I'll bite. How are you using it and what is it doing to help you. I use excel a lot but haven't tried this.

 

Basically if you have any need to import, clean, combine data - or basically do ANYTHING with data, you should be doing it with Power Query.  It's only been around since 2016 - it's a free add-in, but it doesn't seem to be that well known. 

 

It has this user interface where you can connect to data files (in loads of formats) or just to the data in your current workbook, transform it, and then load it back into the workbook where you can either just do analysis on that data, or you can combine it with other data sources, and if needs be create an entire data model. But during the transform process you can unpivot the data and you can add new calculations in. 

 

So if you get some completely fucked up text file, you can tidy it all up, etc. But the kicker is that once you do the connect/transform/load process once, it remembers all the steps you took (like VBA but without the need to know/understand VBA) and will just repeat them on a revised version of the data in seconds. And if you've made a mistake, you just go back, delete the saved step and re-perform it the right way, and it remembers that.

 

There's loads more to it than the above, but that's a basic explanation.  The book that I'm learning it from tries to explain it with these graphs - they're saying you can learn a bit of SQL, a good chunk of VBA, and a shitload of formulas to become a super Excel guru.  With a really long learning curve on all of them.  Or you can learn Power Query in next to no time and get 80% of the impact of all of the above: 

 

image.png.971780b892531ae19dbb15ac47feb5da.png

 

image.png.cfbd165af6c6f6b972e0d248c72ab1dc.png

 

This should help explain it better than I could: 

 

 

 

  • Like 1
Link to comment
Share on other sites

I should have mentioned as well, Power Query works in Power BI too.  So once you've learned it in Excel, you've learned it in Power BI as well.  Two tools for the price of one. 

Link to comment
Share on other sites

1 minute ago, Rayvin said:

Ok yeah this does actually look helpful. I'm building a sheet at the moment that could benefit, I'll give it a shot and report back.

 

Thanks!

 

Nice one, this is a good, up-to-date book (published Nov 2021) if you wanted to learn more about it. 

 

https://www.amazon.co.uk/Master-Your-Data-Excel-Power/dp/1615470581/ref=sr_1_1?crid=2HILCKW7M4Q0A&keywords=master+your+data&qid=1648996327&sprefix=master+your+data%2Caps%2C92&sr=8-1

 

Link to comment
Share on other sites

21 hours ago, Gemmill said:

 

Basically if you have any need to import, clean, combine data - or basically do ANYTHING with data, you should be doing it with Power Query.  It's only been around since 2016 - it's a free add-in, but it doesn't seem to be that well known. 

 

It has this user interface where you can connect to data files (in loads of formats) or just to the data in your current workbook, transform it, and then load it back into the workbook where you can either just do analysis on that data, or you can combine it with other data sources, and if needs be create an entire data model. But during the transform process you can unpivot the data and you can add new calculations in. 

 

So if you get some completely fucked up text file, you can tidy it all up, etc. But the kicker is that once you do the connect/transform/load process once, it remembers all the steps you took (like VBA but without the need to know/understand VBA) and will just repeat them on a revised version of the data in seconds. And if you've made a mistake, you just go back, delete the saved step and re-perform it the right way, and it remembers that.

 

There's loads more to it than the above, but that's a basic explanation.  The book that I'm learning it from tries to explain it with these graphs - they're saying you can learn a bit of SQL, a good chunk of VBA, and a shitload of formulas to become a super Excel guru.  With a really long learning curve on all of them.  Or you can learn Power Query in next to no time and get 80% of the impact of all of the above: 

 

image.png.971780b892531ae19dbb15ac47feb5da.png

 

image.png.cfbd165af6c6f6b972e0d248c72ab1dc.png

 

This should help explain it better than I could: 

 

 

 

 

Lucky for me I'm shit hot at VBA coding, SQL queries AND know a fuck ton of Excel formulas so, by that token, I must be a guru level Excel user. On top of that plethora of skills, I can update Excel spreadsheets using PowerShell so I don't think there's a single word that can define my skill level... although I'm sure by virtue of this post some of you tarts will think of one. :razz:

  • Haha 1
Link to comment
Share on other sites

Definitely going to give Power Query a go. Im

upping my excel skills at the moment and have been on it a few months but this it’s a lot better if there’s a tool to save me bothering :lol: 

 

I’ve got some nasty databases(one is over 2m lines)  at the moment so if there’s an easier way of doing it I’m all ears. 

Link to comment
Share on other sites

Was thinking of getting some excel/word training for the future when I'm kicked onto the dole next year but after reading this thread I'm starting to think it's a step too far. :lol:

  • Haha 1
Link to comment
Share on other sites

1 hour ago, Tom said:

Definitely going to give Power Query a go. Im

upping my excel skills at the moment and have been on it a few months but this it’s a lot better if there’s a tool to save me bothering :lol: 

 

I’ve got some nasty databases(one is over 2m lines)  at the moment so if there’s an easier way of doing it I’m all ears. 

 

Aye you could put this in Power Query, then just load it as a connection into a workbook (rather than as a table). If you load as a connection, the fact that the number of rows exceeds Excel's 1.2m max is irrelevant because you're not trying to display the whole database in a workbook.

 

Then you can just query and analyse the data however you like with Pivot tables or Power Pivot. 

Link to comment
Share on other sites

1 hour ago, Howmanheyman said:

Was thinking of getting some excel/word training for the future when I'm kicked onto the dole next year but after reading this thread I'm starting to think it's a step too far. :lol:

 

My lass is in the same boat. Fucking hates teaching but feels like 20 odd years in that profession has left her without the basic office skills she would need to get out. 

 

She's doing some courses on Skillshare atm, but honestly you could easily get canny good on all the Office apps by just finding some good YouTube channels and following their tutorials for nowt. 

  • Like 1
Link to comment
Share on other sites

7 minutes ago, Gemmill said:

 

My lass is in the same boat. Fucking hates teaching but feels like 20 odd years in that profession has left her without the basic office skills she would need to get out. 

 

She's doing some courses on Skillshare atm, but honestly you could easily get canny good on all the Office apps by just finding some good YouTube channels and following their tutorials for nowt. 

When I saw I had a notification in the Excel thread I thought it would be CT telling me to take up taxi driving instead. :lol:

  • Haha 2
Link to comment
Share on other sites

38 minutes ago, Gemmill said:

 

Aye you could put this in Power Query, then just load it as a connection into a workbook (rather than as a table). If you load as a connection, the fact that the number of rows exceeds Excel's 1.2m max is irrelevant because you're not trying to display the whole database in a workbook.

 

Then you can just query and analyse the data however you like with Pivot tables or Power Pivot. 


Ill definitely give it a go. Is it something I’m gonna need IT to sign off or is it part of the excel program anyway?

Link to comment
Share on other sites

4 minutes ago, Tom said:


Ill definitely give it a go. Is it something I’m gonna need IT to sign off or is it part of the excel program anyway?

 

Just comes with Excel. If you go to the data tab, Power Query is all the Get Data from... stuff. I started learning it about a week ago and I've just laced something that took me an afternoon to fuck on with normally. It took a bit of setting up (I was writing Power Query formulas to do some of the stuff, which I somehow got right :lol:) but from now on I just hit the refresh button and it literally takes that long to make it work. An afternoon a month saved, during which time I'll be sitting watching the telly instead. 

 

  • Like 1
Link to comment
Share on other sites

5 hours ago, MrBass said:

 

Lucky for me I'm shit hot at VBA coding, SQL queries AND know a fuck ton of Excel formulas so, by that token, I must be a guru level Excel user. On top of that plethora of skills, I can update Excel spreadsheets using PowerShell so I don't think there's a single word that can define my skill level... although I'm sure by virtue of this post some of you tarts will think of one. :razz:

 

I'm gonna go with "clever cunt", but see what others think and we'll set up a poll. ;)

 

  • Haha 1
Link to comment
Share on other sites

3 hours ago, Howmanheyman said:

When I saw I had a notification in the Excel thread I thought it would be CT telling me to take up taxi driving instead. :lol:


Funnily enough I did consider that earlier but didn’t have you down as a driver :lol: 

Link to comment
Share on other sites

15 hours ago, Gemmill said:

 

Just comes with Excel. If you go to the data tab, Power Query is all the Get Data from... stuff. I started learning it about a week ago and I've just laced something that took me an afternoon to fuck on with normally. It took a bit of setting up (I was writing Power Query formulas to do some of the stuff, which I somehow got right :lol:) but from now on I just hit the refresh button and it literally takes that long to make it work. An afternoon a month saved, during which time I'll be sitting watching the telly instead. 

 

We're switching from this to Google Data Studio at work. 

 

Also trying to learn enough SQL, that I can make a business case to send me off somewhere to sit exams in it. Which has fuck all to do with accreditation and everything to do with having a jolly on the work dime.

Link to comment
Share on other sites

  • 10 months later...

Right you sexy Excel fuckers.

I have been trying to streamline my cycling league table thing for my club using VLOOKUP.

I have it working sweet for one table.  But I now want it to filter something.  I maybe doing this wrong of course.  Below a couple of screen shots.

First one is a full table of male and female competitors.  Ranked in order of total points.

Second one, I want the same but only famale riders.  However I am not sure how to sort this one?

 

 

 

WLTable1.png

WLTable2.png

Link to comment
Share on other sites

1 hour ago, wykikitoon said:

Right you sexy Excel fuckers.

I have been trying to streamline my cycling league table thing for my club using VLOOKUP.

I have it working sweet for one table.  But I now want it to filter something.  I maybe doing this wrong of course.  Below a couple of screen shots.

First one is a full table of male and female competitors.  Ranked in order of total points.

Second one, I want the same but only famale riders.  However I am not sure how to sort this one?

 

 

 

WLTable1.png

WLTable2.png

What are the tables B5:C41 and H5:I41?

 

 

You'd honestly be better with a pivot table, then you can filter with a click of a button

 

  • Like 1
Link to comment
Share on other sites

I have tried looking at a pivot table.  It does look like its the way to go, but I am struggling to use it.  If I knew how to host a cut down version of my spreadsheet it maybe an easier way to see what I am trying to do.


Basically we have a list of events for the season.  If you do an event you get points.  Its the person with the most points at the end of the season wins.  Thats the jist of it.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.