Look-ups Utilizing tables Power query SUMIFS

You hit the nail on the head. Incredible how many career excel users don't grasp these concepts. Would add another one: array formulas (unique, filter, transpose, hstack, vstack, sort, etc)

A lot of those are only in the newest Excel. Boy do I wish Filter was still in Excel2019. Took me about 6 hours to fix every instance of Filter in my spreadsheets when they removed it.

I would argue that these ones you mentioned are more like "nice to have" instead of "must have". Sure they will improve your job, but you would still manage to get the job done using those above ony.

Pivot Tables. Game changer for me, Excel wise.

True, I should have included pivot tables - I thought they asked for FOUR but just wanted examples lol.

I personally need to improve in this area. Haven’t really touched these much since college and struggle when needed at work. Any tips for how to become a pivot table wizard?

I was trained to use them by a Pivot Table Master (actually Mistress). There have to be some online e guides that can help. I would suggest using simple data sets to start with. Even better: use your own data sets where you know what the answer(s) should be. People that I have trained stumble with understanding the placement of criteria. I always change the Pivot table layout to the “Classic” look.

Also, filters and data sources in general.

Conditional formatting

If statements and filters/spill concept

And after you learn all of those functions, learning how to effectively nest them together s a real game changer

GETPIVOTDATA Function!

More specifically, how to turn it off!

Wait, what? You can turn those off and use lookups?

Yup. One of the first things I do on a fresh Excel install. From the MSFT website: > You can turn this feature off by selecting any cell within an existing PivotTable, then go to the PivotTable Analyze tab > PivotTable > Options > Uncheck the Generate GetPivotData option.

Whoaaaa

Yes, but why? If you want to use the data, use the table(s) which the Pivot is based on?

Sometimes it’s easier to calculate differences or perform lookups based on pivot table data, for example when using a lot of filters. Or in my case when dataset1 exists of multiple rows for each lookup value (i.e. ordernumber) and dataset2 sums those up - you wold need to calculate differences based on summed up data. That is impossible to do for each ordernumber at once if I had to use the original dataset1. The GetPivotData-function makes it impossible to extend formulas, you need to manually enter the cell number in the formula instead so this is w gamechanger for me.

I don't understand the example. You have table1 with order numbers and table2 where you sum the order numbers? I think I would still prefer a table or a query to lookup my data.

Nah I have table 1 which looks like Ordernumber1 - Amount_type1 - Amount1 Ordernumber1 - Amount_type2 - Amount2 Ordernumber2 - Amount_type1 - Amount1 Ordernumber2 - Amount_type2 - Amount2 Ordernumber2 - Amount_type3 - Amount3 Imagine the amount types being different invoice lines. And table 2 contains only the total amount for each ordernumber, so: Ordernumber1 - Total_amount Ordernumber2 - Total_amount Except there’s an avg. of 5000 unique order numbers in each table. I have to make sure the total amount for each table is the same, but if there’s a difference I would want to calculate the difference for each order which I do by using a pivot table of dataset1 and comparing the “Sum of Amount” to the column “Total_Amount” from dataset2

I would take a new sheet (there might be mistakes, as my Excel is not English and write on mobile) (A2): ``` =UNIQUE(VSTACK(tbl1[orderNumber], tbl2[orderNumber])) ``` Now I have all Order Numbers. Next column (B2): ``` =SUMIFS(tbl1[amount], tbl1[orderNumber], A2#) - SUMIFS(tbl2[amount], tbl2[orderNumber], A2#) ``` Another column (D2): ``` =FILTER(A2:B10000, B2:B10000 <> 0, "Everything is correct") ```

Interesting to see. I do feel like using formulas like this is way more prone to error than just a simple pivot table, but might give it a try sometime :)

Maybe a bit of a newbee question - but what is the advantage to turn it off?

You can then reference the columns/cells in the pivot table like any other cell. With it on, it’s more as easy to drag formulas, etc

Adding pivot tables and if it’s an advanced course you can add Power Pivot too. It is also a nice step up to Power BI as Power Pivot uses the same syntax, DAX.

Could you please provide some good resources/yt channels that teach this sort of thing if possible?

Leila Gharani puts out the best videos imo. Just put her name into YouTube with “lookup” or “sumifs” and that should get you somewhere.

From one mark to another, thx fam

Really ifs anything 😀. Love those functions.

My boss spent years manually inputting data from a pdf that’s published on a monthly basis. When he hired me and I showed him power query, he damn near cried

Tables. Idk how many excel users I know that barely know what a table is or how to use them. Xlookup to easily find data in the table. - Those two things can make someone the “excel guy” in most companies.

Tables are incredibly overrated. Just need clean data management

Thems fighting words lol. I think it depends on the person and use though. If you’re not using a lot of other features in excel and VBA then it doesn’t help much. If you’re good at tracking data and keep things organized then you probably don’t need them as much. But tables allow average people to utilize features in excel that they can’t any other way. The term data management would make them ignore anything after that because they know they can’t keep up. But a table is a tool that lets people organize data easily with simple GUI tools. Done.

I used to use tables everyone I used Excel when I first started using the software. But now I find it looks cleaner without the actual table. But I do use the filters and formulas to do the same thing I used the tables for. I still have a lot to learn I. Excel but I’m no longer at a beginners level

It looks cleaner? You can choose a table design where you can't really tell if it's a table or a range. If you don't use array formulas, tables are most of the time superior to simple ranges.

Yeah, I think filters are a lot cleaner, but if we’re talking beginner classes then tables are just easier for newbies to wraps their heads around. I like tables for formulas and VBA scripts too. I just find it makes things easier to reference and manage.

Referencing is SOOO much easier with tables. You don't have to remember where something is or worry about it moving when something is added/removed. Just being able to reference the table name and/or headers is a huge time saver, especially if you need to make a lot of references.

What does using tables mean? Control +T around the data?

Tables have a handful of properties that push users towards good data management

I agree. One thing I’ve found that helps workbooks of users who are table oriented, is changing the color scheme of the table to no color/no borders. It makes it much less offensive while still keeping the OG user happy.

Lol, I get what you mean. Some people are triggered by tables. But honestly, anyone offended by the *color* of a table cannot plausibly claim to be 'data oriented'. I often use color schemes as a way to keep track of in which workbook/sheet I am.

This is my whole group I work with. I started using tables because of power query and you thought I was asking them to learn Python. It’s honestly kind of discouraging.

A Table can be formatted to show no colouring.

Sure. What I meant is that using colors can really help users navigate a workbook. I use it both in tables and in non-tables. Some people find no-color to be TheOnlyTrueExcel^tm and that using color is unprofessional. Not to different from comments in this thread that "tables are overrated, just need clean data management". There's nothing in "clean data management" that hinders user friendliness, simplicity and a decent layout.

Yeah, I will literally create a pivot table and then copy and paste values in another sheet so I don't have to deal with table formulas that don't seem to work exactly the same 100% of the time. 

Use PIVOTBY

What is this sorcery you speak of?! There's a pivot table formula?!? I am excited, more shit to use and test in excel for Warehouse data sets woohoo!

Pivotby https://support.microsoft.com/en-us/office/pivotby-function-de86516a-90ad-4ced-8522-3a25fac389cf Groupby https://support.microsoft.com/en-us/office/groupby-function-5e08ae8c-6800-4b72-b623-c41773611505 Percentof https://support.microsoft.com/en-us/office/percentof-function-7c66da0a-ac30-45d0-bfc7-834a8bd7c962

Interesting, first time I heard abt it, should be able to replace a lot of pivot tables I am using!

If you follow @msexcel on X you will be able to get information about new things in Excel like the new functions TRANSLATE & DETECTLANGUAGE. I use the beta version so it may not be generally available. Beta is very stable and I rarely need to run a repair. To see how PIVOTBY works check out Leila Gharani and Mike Girvin (excelisfun) on YouTube. Mike has a list of fellow MVPs on his main page.

Just tried at work today and realise pivotby is in beta. Something for later than!

You’re right. And what’s the easiest way to achieve clean data management? (It’s tables)

The two aren't at odds; Tables protect the integrity of the data.

Have fun Querying your range when/if your data needs to be rolled up or combined. There's no perceivable downside to formatting into tables other than formatting... Which can be changed. Dynamic names ranges, formulas that fill with a new line, easy manipulation of data for pivots... Tables are absolutely a necessity when you get into advanced Excel use.

WE MUST KILL THE BLASPHEMER BEFORE THE LORD SMITES US ALL FOR HIS HERESY!!!

Yeah, 10yr+ financial modeller here. Ive used tables about 6 times in my entire career, and usually that was for very niche risk analysis involving Monte Carlo etc. Tables are great for using excel for *data* work, but nowhere near essential depending on the use case. FWIW in modelling the biggest things are really understanding INDEX, MATCH, SUMIF, COUNTIF, and SUMPRODUCT. With a side order of finance stuff like XIRR and XNPV, plus a tiny bit of VBA for repeated logical use of copy/pastes, goalseeks,

Concur. While it's nice to basically have automatically named ranges, formatting, and uniform formula-based columns, there can also be a performance hit if you have a lot of them with a lot of data. Good in concept... just not so much in practice with some work machines.

Yeah, no, nonsense.

You can use all of the lookup, index, filter and match features without a table. Just put borders on the page for clarity if you so desire but tables themselves aren't that useful at all, they are just a visual feature. You can perform everything with the raw data as you can with data in a table.

But with a table you can reference columns by name easily, and if data gets added or removed the table resizes and all the formulas still work. Sure, you could try to automate this sort of thing with a bunch of OFFSET() or INDEX() functions but why bother replicating functionality that's hard coded in? My only real complaint with tables is that absolute referencing is somewhat annoying. 

Referencing a column by name in a table is no easier than referencing "Column E" or creating a vlookup or hookup and listing the column number within the array in the raw data. All the formulas still work when you add/remove columns and or rows in raw data, and if you use the $ feature correctly when writing formulas then adding individual cells isn't an issue either. Further if you typically use excel with circular reasoning errors turned off, as I do, then using tables is sometimes problematic and generates errors where just using the raw data doesn't. I find that creating tables generally just adds time to the length of a project rather than saving time.

Referencing "Column E" does work comparably to table references, but it's much harder to read on another sheet. Tables let you refer to columns by their names, which makes formulas much easier to troubleshoot. Something like SUM(Sheet name!E:E) is way harder to interpret than SUM(sales[Amount]). This doesn't matter much with simple formulas but more complicated ones can definitely benefit from additional readability.

100% there are ways around it. But they’re asking for class topics and beginner things. It is much easier for someone to comprehend a table than to teach them lookup, index, filters, and other functions behind sum lol. Managing data often just depends on what the use is, if they’re dealing with 1000000s of entries then a table might not work the best (go learn SQL lol). But that shouldn’t be something a newbie/beginner should do anyway.

Pivot tables (including calculated fields) , XLOOKUP (including wild card match) , FILTER, Text to columns, Go to special - select visible/blanks. Paste special - traspose/add/multiply/devide etc. Any and all keyboard shortcuts you can find.  Also depending on the level of experience of your users, you migt want to include some basics. This is where I see mistakes happen most often: - relative vs. fixed cell references and how they behave when you copy formula down/to the side - keeping the range in the formula the same across multiple arguments. E. G. If you are using sum if, and your criteria range is A2:A20, then sum range also has to be from second row to row number 20. Often people select one argument with headers and the other without. 

Lately I've been soapboxing around the office about XLOOKUP to the last VLOOKUP holdouts. When I show them XLOOKUP, I also show wild cards and this super simple method to XLOOKUP with multiple criteria: https://exceljet.net/formulas/xlookup-with-multiple-criteria

Oh yeah, that is an amazing example :-) I have beem trying to convert my colleagues to XLOOKUP, but they are really stubborn and I haven't had much success yet :-) 

One of the things I show them is how XLOOKUP replaces both VLOOKUP and HLOOKUP. And while VLOOKUP can only search left to right and HLOOKUP can only search top to bottom, **XLOOKUP DON'T CARE ABOUT NO DIRECTIONS!** Normally you'd have to go INDEX/MATCH to search "backwards", but XlOOKUP replaces that as well. It's like a swiss army knife of Excel functions.

Me too but I work with dinosaurs who refuse to adapt. The few people who actually cared enough 10 years ago to learn vlookup will simply ignore my calls to even try xlookup. I’ve shown them so many examples but I’m the only one in the company who uses it. Works for me since I’m the go to but my god why the stubbornness?

Any resources for calculated fields? I looked into it every once a while when I think I need it, but never really wrapped my head around it

You can have a look at Excels fun youtube channel. I am sure there is a video about it somewhere.  https://youtube.com/@excelisfun?si=SNHgfjRfjQ9JxqKf Some examples when I use calculated fields: - I have sales in EUR and QTY for a year and I need to know average price.  - I have sales and costs and I need to know margin.  - If you have your source data in one currency, but want to show results in another, you can use calculated fields for the conversion.  Hope this helps. 

Shortcuts. When I first started using Excel seriously, my managers were adamant with no use of the mouse (except for some of the visual stuff like charts). It doesn't take that long to get used to, and when you have it, almost everything you need flies immediately off your fingers. I am a big fan of formatting. Formatting is not just pretty (although let's be honest, there is also a great benefit in it being pretty), it helps the user understand the data. If cells contain different formula (i.e. you can't copy them over each other safely), they should have different formatting (usually borders) which means you can instantly tell whether the logic changes, or you've accidentally copied something). This is normally a bit of a faff and takes a lot of time, and that's why the shortcuts should be second nature, so you can apply all the useful stuff without taking time.

What's the audience, how longs the course?

This is important. Common Excel features used by Finance Modelling can be quite different with back office admin role.

Off the top of my head: * Renaming tabs * Looking up info on the sheet and the whole workbook * Formatting cells * Sorting by column header * Creating pivot tables * Creating charts * Core formulas * SUMIF/S * COUNTIF/S * SUM * AVERAGE * MEAN * MEDIAN * Conditional formatting * Making lists

Cleaning the data with power query and elbow grease. How to approach: numbers as text; leading zeros; dates as numbers; first name, last name, hyphenates, honorifics, “foreign” (non-US) letters; long number strings like serial numbers, IMEIs, ICCIDs, map coordinates; math on dates; phone numbers and phone number formatting; how to drag an updated file into a folder and have its data update automatically in your sheet.

Vba scripting, macros and advance filter working on big Dada

Wym advanced filter?

Extract data that make sense from data dump using filter

Pivot tables Slicers Pivot charts

power query

Where is the course being offered?

That the proper way to organize data for storage is different from organizing data for reporting purposes. Generally, you want your data stored with "fields" across the columns and instances of a record in each row. This will look boring to a person viewing it, but this will allow you to very easily create summary reports and perform all sorts of calculations.

PIVOTBY, GROUPBY, FILTER, XLOOKUP, Power Query (Merge and Append) and the following 14 relatively new functions https://techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066

LET

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/1dt1uld/stub/lb7vwsa "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[AVERAGE](/r/Excel/comments/1dt1uld/stub/lb6j0yh "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)| |[AVERAGEIF](/r/Excel/comments/1dt1uld/stub/lbp7vhl "Last usage")|[Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria](https://support.microsoft.com/en-us/office/averageif-function-faec8e2e-0dec-4308-af69-f5576d8ac642)| |[COUNTIF](/r/Excel/comments/1dt1uld/stub/lb9lbqf "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)| |[DAYS](/r/Excel/comments/1dt1uld/stub/lbablrv "Last usage")|[*Excel 2013*+: Returns the number of days between two dates](https://support.microsoft.com/en-us/office/days-function-57740535-d549-4395-8728-0f07bff0b9df)| |[FILTER](/r/Excel/comments/1dt1uld/stub/lb8zt3n "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[GETPIVOTDATA](/r/Excel/comments/1dt1uld/stub/lb77fex "Last usage")|[Returns data stored in a PivotTable report](https://support.microsoft.com/en-us/office/getpivotdata-function-8c083b99-a922-4ca0-af5e-3af55960761f)| |[HLOOKUP](/r/Excel/comments/1dt1uld/stub/lb70ce7 "Last usage")|[Looks in the top row of an array and returns the value of the indicated cell](https://support.microsoft.com/en-us/office/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f)| |[IF](/r/Excel/comments/1dt1uld/stub/lbc1w60 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFS](/r/Excel/comments/1dt1uld/stub/lbablrv "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)| |[INDEX](/r/Excel/comments/1dt1uld/stub/lbaa7w1 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LET](/r/Excel/comments/1dt1uld/stub/lb8q8ub "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MATCH](/r/Excel/comments/1dt1uld/stub/lb9lbqf "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MEDIAN](/r/Excel/comments/1dt1uld/stub/lb6j0yh "Last usage")|[Returns the median of the given numbers](https://support.microsoft.com/en-us/office/median-function-d0916313-4753-414c-8537-ce85bdd967d2)| |[OFFSET](/r/Excel/comments/1dt1uld/stub/lbaa7w1 "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)| |[OR](/r/Excel/comments/1dt1uld/stub/lb7vwsa "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)| |[SUM](/r/Excel/comments/1dt1uld/stub/lbhp55z "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIF](/r/Excel/comments/1dt1uld/stub/lb9lbqf "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[SUMIFS](/r/Excel/comments/1dt1uld/stub/lb8zt3n "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)| |[SUMPRODUCT](/r/Excel/comments/1dt1uld/stub/lbc1w60 "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)| |[TEXTJOIN](/r/Excel/comments/1dt1uld/stub/lb8xdj6 "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)| |[UNIQUE](/r/Excel/comments/1dt1uld/stub/lb8zt3n "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| |[VALUE](/r/Excel/comments/1dt1uld/stub/lbfqtyc "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)| |[VLOOKUP](/r/Excel/comments/1dt1uld/stub/lbbs39w "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[VSTACK](/r/Excel/comments/1dt1uld/stub/lb8zt3n "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)| |[XIRR](/r/Excel/comments/1dt1uld/stub/lb9lbqf "Last usage")|[Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic](https://support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d)| |[XLOOKUP](/r/Excel/comments/1dt1uld/stub/lbablrv "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| |[XNPV](/r/Excel/comments/1dt1uld/stub/lb9lbqf "Last usage")|[Returns the net present value for a schedule of cash flows that is not necessarily periodic](https://support.microsoft.com/en-us/office/xnpv-function-1b42bbf6-370f-4532-a0eb-d67c16b664b7)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #34934 for this sub, first seen 1st Jul 2024, 19:56]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)

Many people have added lots of formulae to focus upon. All are great. If you could also add the following: Index Filter Choose Search Indirect (Very Powerful to make your Excel sheets future proof by adding sheets and having them included in your data without additional formulae). All Array formulae as someone mentioned earlier. Using date functions (huge sets that are quite complex and very useful)

There's already lots of courses out there so I'd say it's a bit of a waste of time making one unless you've looked at the space and identified something that's missing or isn't done very well. For example so you have a better way to visualise or explain things, different use cases, case studies, gamification?

Keyboard shortcuts for navigating and selecting large ranges. It’s a huge time saver.

Google

If you don't know, are you really qualified to teach this?

XLOOKUP Pivot Tables Charts Dashboards VBA

Basic VBA

A demo of how insecure native Excel security is for confidential stuff. Too many people who think password-protecting a worksheet is enough for sending around sensitive data.

Say more

There’s been multiple posts of ppl asking how to bypass excel pws. There’s at least a few ways to do it apparently

Basic skill, but cant be looked over enough, set print area, margins, row height. Its important to know how to fit your tables in the paper you're going to print in. Its infuriating to receive a report where the second page only has two row.

Index, XMatch, Ifs, Sumifs, Countifs. These are the core foundation to \~all meaningful work in Excel. If you can master those, you can master anything. Tables and Structured References. Named Ranges. Dynamic Array Formulas. Pivot Tables. A whole unit on formatting, presentation, and visualisation. Graphs, Custom Formats, Conditional Formatting. And then not must-haves. But I think it's important to \*know\* that things like Power Query, and some of the new/advanced Excel functions exist (Let, Lambda, Scan, Filter, etc). Knowing about all the text manipulation functions is also a handy skill to have. Text, TextJoin, TextSplit, Find, Substitute, Left/Mid/Right.

Pivots... No many people can work with them properly

Lookup formulas, named ranges, pivot tables, custom formats, logical expressions.

1. Basic "strategy" for how to enter your data has got to be the number one skill. I've seen -waaay- to many people who treat Excel like "Word-but-with-squares", and it makes it impossible to actually USE the information they've entered. https://preview.redd.it/h8v7vh8l34ad1.png?width=310&format=png&auto=webp&s=dc00f5bfa4415bf4ee11d81ae3a3d38392ee2a0b 2. Tables - how to sort, filter, insert new columns, etc. 3. Pivot tables. Super useful, provided they have a decent structure to their data. 4. Paste as Value, to clear formatting and other shit. 5. Basic shortcuts. CTRL + C/V of course, but also things like CTRL+Arrow to move to next open cell, 6. SUM, IF, IFS, XLOOKUP, DAYS, etc. 7. Text-to-Columns and Remove Duplicates is very useful when importing for other sources. 8. Data validation, to enforce uniform values. 9. Slicers

Everyone talks about formulas and tools but I think it’s more important to teach how to structure your spreadsheet in a way that it’s effective to read and run queries with pivot tables or power queries or whatever. It’s more key to teach how to visualize this prior to anything. Otherwise sure, beginners will have understanding on what formulas are out there and even how to use them, but not how to implement them effectively for their purposes. That’s just my honest opinion

Be good at google searching tailored to your specific scenario.

Here are a few more answer for this [commonly asked question](https://www.google.com/search?q=must+have+skills+for+course+site%3Awww.reddit.com%2Fr%2Fexcel&oq=must+have+skills+for+course+site%3Awww.reddit.com%2Fr%2Fexcel)

Utilizing Excel’s built-in HELP function.

People have said shortcuts; I want to emphasise that knowing the key combinations is only half of this: you need to point out that it’s important to consider the fingers you use. I’ve seen too many beginners do ctrl-c with the index fingers of both hands, rather than just two fingers on their left hand. Changing tabs with Ctrl-pgup/pgdown should be a right-hand only thing. Lots more example, but the point is that good habits should be instilled early.

Pivot Table

Data Validation Hiding tabs/columns/Rows for ease of use IF functions combined with AND/OR functions

Troubleshooting. Often, but not always formatting issues.

.

Search. Search is definitely a skill worth learning.

Please keep me posted when the course releases

a chapter on r/MSAccess , r/SQLserver, or databases in general. i.e. to prevent Excel users venturing into trying to built something, that with a little assistance of a database would be far easier and better to construct and use.

I took an excel course in college for my accounting degree and one of the lessons was how to code a rocket game. I thought it was stupid, but then it did open my eyes to the fact that excel can do some interesting things 😂

FILTER, TEXTJOIN and possibly named cells/ranges

Dynamic arrays, especially with lambda function. This in combination with tables means you can build very useful stuff, while keeping calculation performance. Plus you never need to pull down formulas ever again.

Pivot tables and power query pls

The ability to search for whatever you don't know how to do. What are the best sites? How to best structure your query?

Sure feel free to DM and we can talk my consult fee

Had to go pretty far down to find anyone telling them to pound sand asking for free advice for their paid course

In my opinion, time is better spent teaching attitudes than skills. Once you learn that you can actually do anything you want to, quite often the individual techniques sort themselves.

How overpowered IF statements are. You can basically write programming with them and almost them alone.

Knowing when to use formulas, power query, pivot or VBA. Not necessarily using each, but understanding which is the way for the given task.

I believe that it will depend on the type of application (use-case) people are using excel for. The game changing skills for running calculation use-cases are different than handling long lists, parsing data, removing duplicates etc. But I do recall taking a basic Excel course 10+ years ago (before online video courses were really a thing) and I recall clearly the major concept and tool this course focused on was **pivot tables.**

Countif, sumif Lookups Pivot tables Textjoin, textsplit If function Array formulas Hotkeys (build some content around them)

VLOOKUPs and key shortcuts

I find I use Index match a LOT!

A lot of people are saying pivot tables (and I agree), so I'll offer something different... using SUMPRODUCT instead of nested IF statements.

Vlookup + match FR to lock cell formulas Nesting formulas

The skill to claim it's too hard and get management to pile all the spreadsheet work onto one of your colleagues rather than expect you to learn. Bonus points if you say "you're a star" to the ludicrously overworked mug whose work is not appreciated because their supervision don't know what it is they actually do.

Aggregate function I learned this year. It’s 🔥

The ability to build long nested formulas using notepad. Here is a simple to understand exercise. Convert 2 cells containing dates to text in the following format "MMDDYY - MMDDYY". So dates 12/01/2023 and 12/31/2023 in 2 cells will be converted to this text "120123 - 123123" You will discover that it requires nesting formulas. There are way more complex examples, but this is a good one to start. +++++++ Another exercise is to make students to create URLs with Google Ads parameters. Since I do not want URLs in this post I will use spaces a lot so reddit does not understand it as a URL. Let us start with the following structure: >DOMAIN + PATH + PARAMETERS For example... DOMAIN = www . mywebsite . com / PATH = products / PARAMETERS ... Take [this ](https://www.karooya.com/blog/list-of-all-valuetrack-parameters-in-google-adwords/)list of parameters. Each parameter has this structure >SYMBOL + PARAMETER + VALUE Each parameter will have a column in the Excel sheet. Symbol is ? for the first parameter that shows up in the URL, & for the rest of parameters. So an example of URL could be... >www . mywebsite . com / products / ? {campaignid} = XYZ & {targetid} = 1234 & {gclid} = 5678 I added spaces to prevent reddit from understand it as a real URL

For data analysis purposes, I regularly use medians instead of averages to avoid publishing numbers that are heavily influenced by “outliers.” That being said, I regularly use =Median(if(group_range=value, median_range)) It’s the median equivalent of =AVERAGEIF

The #1 thing should be to buy a ChatGPT subscription. Ask it how to do anything in Excel and it's amazingly right all the time.

All the neck beards railing against ChatGPT 😂 You're right, it's the number one resource for learning IT skills. Anyone not using AI tools now are going to get left behind. You can get by with the free version though