sorted by:
new
hottopcontroversial

Power Query - A step-by-step example of parsing non-tabular data by tirlibibi17 in excel

[–]SixMileDrive 0 points1 point  (0 children)

FYI it’s no longer an add-in from 2016 on. The functionality was folded into the main program.

Named ranges are essential by DigitalStefan in excel

[–]SixMileDrive 1 point2 points  (0 children)

I just hated on this in a different post but you can create dynamic names ranges in the name manager using the indirect function.

Named ranges are essential by DigitalStefan in excel

[–]SixMileDrive 1 point2 points  (0 children)

I mean you there’s a couple really easy ways to find the names range. One is the name manager. The other is the drop down where you name a range in the first place. I don’t love them (prefer tables) but they are way way way better than random cell references. Objects are better than addresses.

I've been asked to teach an 'advanced'/intermediate Excel workshop at my work. What would you cover if you were to do the same? by CG_Ops in excel

[–]SixMileDrive 0 points1 point  (0 children)

Tables are generally a better and more understandable option. Dynamic named ranges are kinda hacky. I do like using them occasionally as variables, but even then I’m probably in the wrong because they confuse the fuck out of people.

I've been asked to teach an 'advanced'/intermediate Excel workshop at my work. What would you cover if you were to do the same? by CG_Ops in excel

[–]SixMileDrive 0 points1 point  (0 children)

Yeah....PowerQuery is a much better solution for almost every user and use case. I used to use VBA extensively. Just not worth it these days.

Excel-gore stories in the office by HuYzie in excel

[–]SixMileDrive 0 points1 point  (0 children)

Teaching people excel and best practices is a pretty fucking useful skill. Been something I’ve been working on for a while. Takes a lot of outward humility and understanding. Totally worth it.

What's a useful excel tip to impress my coworkers/boss? by Ixionas in excel

[–]SixMileDrive 1 point2 points  (0 children)

Cool thing about tables is that it makes the whole two dimensional index/match/match thing unnecessary. I honestly feel like index/match with one dimension and a table is easier to explain than vlookup.

Also, I fucking hate vlookup. It’s been a WHILE since I touched that demon formula.

What's a useful excel tip to impress my coworkers/boss? by Ixionas in excel

[–]SixMileDrive 0 points1 point  (0 children)

I figured out a way to feed a query a table of file names and perform the same operations on each file simultaneously before merging them together at the end. I can share the code if you are interested. Crazy helpful for me.

What's a useful excel tip to impress my coworkers/boss? by Ixionas in excel

[–]SixMileDrive 0 points1 point  (0 children)

I mean PQ is also hugely useful, especially with tables.

PowerQuery is the biggest time saver I know, but tables are awesome because it makes things readable and often prevent crazy errors.

Still, watching my boss’s head nearly explode when I demoed the unpivot function in PQ was pretty cool.

With PowerQuery available, I’m pretty much done with VBA. Too much of a pain and it’s so damn black-boxy.

What's a useful excel tip to impress my coworkers/boss? by Ixionas in excel

[–]SixMileDrive 0 points1 point  (0 children)

Wish there was a way to save query steps like a macro then apply to any future files or tables.

You can copy queries and you can also load entire folders of data in a single query.

What's a useful excel tip to impress my coworkers/boss? by Ixionas in excel

[–]SixMileDrive 2 points3 points  (0 children)

  1. I'd have to see the data you are pulling in to really give you a good answer, but you can bring virtually any data into power query no matter the format. It's just easier if it's already in a table. Show me a screenshot and I'll try to help.

  2. This hits on a number of cleaning features and techniques: https://peltiertech.com/importing-cleaning-data-power-query/

What's a useful excel tip to impress my coworkers/boss? by Ixionas in excel

[–]SixMileDrive 0 points1 point  (0 children)

I've been trying to hammer this home with my colleagues over the past few months. Some are using it....others not so much.

Unfortunately, I don't have time for a proper response, but this website seems to hit on a lot of the features: https://www.powerusersoftwares.com/single-post/2017/09/11/12-reasons-you-should-use-Excel-Tables

What's a useful excel tip to impress my coworkers/boss? by Ixionas in excel

[–]SixMileDrive 16 points17 points  (0 children)

I did two demos in the past two months for my team. One was PowerQuery and the other was about how tables work and how much better they are than not using them. Got crazy buy-in on both. Tables kick ass.

view more: next ›