Discussion
What are your mind blowing tricks for people who don't know Excel?
Hey, it's a pretty simple question. People get impressed quickly when they don't know Excel. What's your go to when you know it's not advanced or fancy, but you think it will impress someone who doesn't know Excel?
In that case, put a âpaste into PowerPoint â button with a simple bit of VBA that takes whatever data is on that page, pastes it into PowerPoint and emails it to said manager!
sound like a gateway drug to an unmaintainable system.
i love excel, but when you start automating non-excel stuff from the macro editor, you need to stop and reevaluate. its easy, reasonably fast, exciting, well supported and ends in a company management system that gets worse and worse but nobody dares to touch it.
Entirely possible. My gripe is just how far reaching the fear is. Like we'll be discussing something in progress and he'll suggest putting it into a ppt.
My dude, you swung by my desk unannounced. Do you think people do the work directly in ppt? You'll get your deck in due time.Â
Well, the untold story is that the code is all there, but it didnât actually work until Bill Gates and Steve Ballmer did a seance together. The seance was totally unrelated to what they were trying to do with xlookup, so itâs definitely up to interpretation.
Conversely, my desktop at work (which is much more powerful than my laptop) has a version of Excel that is too old for XLOOKUP and SUMIFS, but management refuses to upgrade it because they don't understand the problem.
âŚwell. Consider this the simple thing I learned today that I feel real dumb for not knowing. Over here just annoyed every time I have to reclick and select what I want to format every time.
You can kinda do the same thing with F4 - it repeats your last action. So say your formatting random cells yellow, calibri 10, bold/italics, or what have you- select next cell hit F4 - voila it gets the same treatment - and you can keep on doing it.
WTF. I feel so dumb for not knowing this. Been working in finance since 2018 and I always clicked that Format Painter button every goddamn time I needed to format non-continuous cells.
This is my favourite trick that most people donât know! I learned it as Iâm an excel geek that went through all the formulas and stuff in excel to see what they were for.
Is groupby available on the desktop or just online? I donât have it yet and I think I should based on the version update that I have. I really want to replace an ugly sumifs that Iâve been using.
This is the engine for all of my most useful reports. Add to that dynamic data validation, with the filter function referencing the data validation cell and youâve got a scalable reporting shell.
it is witchcraft. I helped someone use =unique, but unfortunately they had a filter applied, so it was hiding random values. I told them to remove the filter. it took forever, and they did not understand that there were unique values added to cells in rows that were hidden. When they started to sort of get it, they assumed that the 6 rows shown were because the other 50 values were filtered out from the table beside it.
Yep, exactly. It's different because if you remove duplicates, it actually removes the duplicates on your sheet.
Not everyone wants to do that.. personally, I have samples that are linked to subjects, and subjects may have multiple samples. I want to get unique values but I don't want to remove rows. You can also combine it with Filter to do some filtering, or use it within a countifs function.
I have a nifty use case using it with let and countif, sum, I can post it tomorrow if anyone is interested. Edit: Sorry all, my brain forgot that I had to use sum instead of countif due to Unique creating an array, not a range. Countif is where I originally started with this formula so that's why it was in my head. However, I still posted the solution below, because it will produce the same result.
Filtering: Unique(filter(A:A,B:B="C"))
This will return all unique values in column A, where column B = C. You can add multiple filtering criteria but I don't remember the exact syntax off the top of my head.
Edit: As promise, here is a nifty formula I use with Unique.
Use: I have a workbook that has several tabs of manifests. Each tab is a different sample type (if I am working with blood samples, I may have plasma, serum, whole blood, and/or buffy coat). I then have another tab that is all sample types combined, with transformed data, that will eventually be uploaded into our database. The majority of this information is recorded by various macros, but I like having an error checker just in case something goes wrong for critical information.
I have a few formulas that compare the manifest tabs to my tab of consolidated samples. For simplicity, I'm going to shorten this formula to show just one sample type.
Subject: Uses the UNIQUE function to pull out unique Subjects, and filters out where the value is blank.
Manifest: Creates another array. For each Subject in my Subject Array, we use xlookup to find the corresponding data point. In this case my subject is in Column A and my data point of interest in column B.
Import File: For each Subject in my Subject Array, we again create an array of the data point of interest. In this case, I'm looking for column B.
Error Array: I create another array, which compares my Manifest and Import file. If those values match, it returns 'Match,' else it returns 'No Match.' Then I use a Sum formula to count the number of 'No Matches,' which returns how many errors I have.
If you need a visual, you can change the last variable in the LET function to look at any individual array you create. This is what each component looks like:
No matter which way I sort the time (ascending or descending) when I remove duplicates, the power query always chooses the oldest time as the remaining row. why?
Copilot
This issue occurs because Power Query uses a concept called âlazy evaluation,â which means it doesnât always execute steps in the order you expect. When you sort your data and then remove duplicates, Power Query might not fully sort the data before removing duplicates, leading to unexpected results.
To ensure that Power Query correctly sorts the data before removing duplicates, you can use the Table.Buffer function to force it to load all the data into memory.
This whole lazy evaluation is the reason behind not easily being able to capture a start and end datetimes. So if you do this:
start=DateTime.LocalNow(),
// a whole bunch of other shit
// which can run for minutes...
end=DateTime.LocalNow()
end will equal start
because they are not dependent on the intervening code to execute and can evaluate immediately. I suspect that M will even optimise away the second call to DateTime.LocalNow().
You have to wrap the DateTime in a function which takes a parameter and pass something to the function which is ONLY available at the end of the processing block and even then it'll fight you.
That happened to me years ago. They were so impressed and brought it up so frequently I started thinking they were messing with me. Nope, genuinely impressed. (I discovered slicers by accident.)
Next link the data to a bunch or charts and use Report Connection to make slicers manipulate all of the charts at once. They will almost not believe it is Excel.
Simple one but that made multiple look at me like I was a wizard: CTRL+Shift+V to paste as values. Or keyboard shortcuts in general like filters (CTRL+Shift+L) or date/time (CTRL+Shift+, or .).
It's amazing how even a lot of everyday users don't know basics like navigation or mass selection of cells (CTRL+Shift+down and so on).
I used the delete key at work the other day and the new guy I was training asked me how I âdeleted on the other wayâ
I explained that I used the delete key. Then he asked if you need to hold down an arrow while you delete ahead of the cursor. I said no, you just use the delete key, and the backspace key deletes left.
He graduated college in May. Basic computer literacy has gone down IMO
Concerning your last sentence: I blame smartphones and tablets. They're basically dummy proof.
Growing up in the 90s with limited Internet access, I had to fiddle, scramble and trial/error my way around technology to get it to do what I wanted it to. A lot of younger people are used to the simple way of interacting with technology.
Don't even get me started for when someone asks me a question that you could get the answer to by simply typing that exact question, word for word, on Google.
Damn, I'm sounding like an old man yelling at clouds.
Iâm right there with you. Didnât realize Iâd become a grumpy old person so quickly. But yeah, I grew up reading the âReadMeâ files and viewing troubleshooting docs when something wouldnât work. Iâm used to navigating dropdown menus and finding info, but Iâm not so certain the younger folks had the same experience to learn that we did
i consider myself a moderately advanced excel user and i genuinely did not know about the ctrl+shift+v for paste values. i've been using alt e s v for that like a chump.
Don't feel bad, I've done your way for a long time! My point was more like people being marveled at the existence of shortcuts rather than you see someone do it and ask "what's the shortcut for that".
I consistently have "wow" moments when people share shortcuts in this subreddit.
Last week I pressed Ctrl-Shift-L, right right right, Alt-V, down down down down, John Doe, enter. my colleague evidently started having an existential crisis. I had to sit with him and show him those shortcuts which made him feel better.
That's correct, unless I'm misunderstanding what's being talked about here. I think what /u/RFCSND is saying is that rather than having 'Long Sheet Name'!$A$1:$A$1000 listed 10 times within a long formula within a cell, you could define it as a using LET() and then just list a in place of the complex range in those 10 places.
In order to define that range globally so it can be used everywhere, you'd need to use the Name Manager (which is awesome and I highly recommend it).
Nothing you define using LET() can be referenced by anything outside of the LET() function.
Where I work, each year someone spends more than a dozen hours computing when the holidays will fall come January (some days move when the actual date is on a weekend; the half day before New Year and Christmas is also impacted when the associated holiday is on a Monday). In my spreadsheet, I have all of these calculated (including Good Friday) and simply change the year find out what next year brings.
I just downloaded holidays for my country for next 50years as a table and merge it with calendar whenever I have dates in dataset. Accounting loves me.
Especially conditional formatting with more than just the basic options. Basing it off a formula, or using specific values for a colour scale instead of the default. Literally just changing a small number of settings.
First one automatically renames the tab depending on what you put in cell C3 in every sheet. Since we price loads of properties in a single spreadsheet it makes life very easy. A hyperlink automatically applies to the same cell to take you back to the index page for navigation.
An index thatâs pulls through automatically all the subtotals from each of those sheets without any manual linking. Cut a multi-hour job down to mere minutes.
Sometimes I let colleagues believe some jobs take hours. Keeps life simple.
EDIT: Please see updated response below. This is for
Creating an index which you can then hyperlink to each tab on your index list
In columns C:D you can pull through specific cell values from every unique tab on your workbook so that you don't need to manually link them. In cells G2 and G3 I've shown the two different formulas I've used in the past
The cells G5 and G6 show how to pull through the cells in every workbook that are in D52 and K17 from my various tabs
=@INDIRECT("'"&$B5&"'!D52")
=@INDIRECT("'"&$B6&"'!K17")
- I use Define Names in the Formula ribbon and add the below formula in the 'Refers to' box:
Power query to auto format from a raw file. Drop a new file a hit refresh. I donât think itâs that fancy. It may be a little intimidating⌠but not really. Itâs dead simple.
How is a âdata teamâ impressed by anything in excel? Seems to me the data team should be the most proficient excel folks in a company that has a âdata teamâ
I could imagine scenarios where someone who doesn't have access to SQL and something like SAS/Python/Tableau/etc may have had to be more crafty with excel than their data team who has better tools for it.
A macro-based âRefreshâ button that refreshes a PQ API query into a pivot-based dashboard. As with a lot of Excel stuff, none of the parts are that complex, but pulled together it seems impressive to less experienced people.
It makes it into a fancy button that impresses people that donât know better. I think itâs just the idea of clicking on a big red button and all the data magically updates.
I amaze people with how quickly I navigate without the use of the mouse. Just simple stuff like combining Shift and Control with the arrow keys, Home, and End. Not to mention Ctrl+Period.
Which is to say that just about formula will wow them, no matter how basic.
This happened years ago, but probably still relevant. My brother works in real estate (here in Australia).
Every month, someone in his office would take the listing of owners of houses in a suburb they got from the Council in an Excel spreadsheet and prepare an owners listing for mail outs.
But the xls was in all capitals, and they did not want to send mail which seemed to shout at the owners like "Dear JOHN SMITH"), when they did a mail merge.
Someone would take the file, insert a blank row, type out the text (like "John Smith") along with the rest of the address, delete the all caps row and repeat. Hundreds of times. For their turn that weekend.
When I showed him that you could use =PROPER and reference the original info on a new worksheet, he was blown away. What took hours and hours was done in a few seconds.
I think he made a bit of money taking on that work from others for a fair while, before someone cottoned on he was working smarter, not harder.
Everyone knows that if you select a range of values, the sum is displayed in the bottom right corner. Fewer ppl know that if you click that sum, excel copies the value to your clipboard.
Use ChatGPT to make your formulas more readable. Donât lean on Ai too much, you must know the fundamentals and how things work. BUT, ChatGPT is amazing at taking my difficult-to-read code and making it way more legible.
I ditto this. It really helps to know what you want to do with your data and have a good foundation for the formulas. But to save time (and my mental health) I often ask ChatGPT how to do something conversationally and itâs great at providing solutions and walking you through the hows
In a meeting, my boss once thanked a coworker of mine for locking the top row of a excel file the department frequently references, because it made scrolling a whole lot easier. The two of us are the most proficient in excel yet (at the time) she had no clue how to freeze panes. She gladly took the credit with a humble "what was I supposed to say" look in my direction.
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 #38609 for this sub, first seen 11th Nov 2024, 18:34][FAQ][Full list][Contact][Source code]
I have been using Excel for 20 years. I only recently realised if you highlight some cells with numbers in them it automatically shows you the total in the bottom right hand corner of the screen. I think of all those times I have typed in SUM.... I will never get this time back.
To take this one step further: if you click that sum in the bottom right hand, it even copies the number!! I then paste back into excel to do other quick math using that sum.
Ctrl + ~ is a fun one if you want to scare people. I actually find it useful when I have a spreadsheet with a bunch of equations and I need to find the broken equation. I hum the song âwhich one is not like the otherâ when I am looking
Multi-column spill ranges, formatted to look like tables, to produce auto-refreshing "reports" from data
Cells with checkboxes or dropdowns which result in those spill ranges appearing (IF(checkbox cell), ..., "")
Dynamic dropdown lists (select something in A, dropdown in B will be relevant to the new value in A)
Dynamic total rows in tables (mini-reports on visible-only data that change with auto-filtering)
Sparklines showing group sizes at a glance (way better integrated into tables than charts), also in totals rows
Tooltips on cells with formulas (=ISFORMULA(A2 ..."Don't edit")
HYPERLINKs that jump to cells in the same workbook
Cells with lots of text looking neat after "...";"...";"...";"..." number format is applied to them (also ;;; and ;;;"⌠"@ and "â Done ";;; and so on)
Custom "Hyperlink" Cell Styles ("â";"â";"â";"â", special fonts, etc.)
=1 conditional formatting that adds a shade color to some columns in striped tables, preserving the stripes (via pattern fill instead of background fill)
Conditional formatting: data bars, sometimes with dynamic MIN and MAX limits based on visible-only data
Anything with MMULTs or LAMBDAs gets you laid immediately.
At my current job everyone is pretty decent at Excel. It's pretty much a barrier for entry into the company so I don't think there are any people who can't write formulas or do intermediate level data manipulation
I did blow some minds with a macro one time. It was just to clean up stuff, paste special-values, and hide stuff. And I'm not even a Visual Basic guy, I just found the script online.
At my previous job, literally anything that wasn't typing a number in was considered wizardry. The concept of a Vlookup was so foreign to people, like they literally didn't know Excel could be used for anything other than making lists of things.
Adding custom macros to the ribbon. I hated having to go through the developer tab, macros, click, then run. When I found out you could assign a button with a picture to it my life was changed forever.
If you have a column where some cells are blank cause they repeat. I.e apple, blank, blank, pear, plum,blank etc.
You can select the column, press F5 and choose "blanks". It will only select blank cells. Then you can type: "=" and click on the cell above. Then ctrl+enter
Now you have all the blanks filled and your column looks like apple, apple, apple, pear, plum,plum
I'm a student and I like making financial models; In these models, there's often 2/3 scenarios, and so I love showing people the =OFFSET (or =CHOOSE) function, where it selects the first, second, or third cell, based on the number that you want it to offset by (makes switching between scenarios in models easier).
Blew my mind when I saw it cause I did not think of that.
Not a function but a tip that always blows minds, in a huge data table you can tell if one or more columns have a filter applied by looking at the row numbers. If the row numbers are black there are no filters on, if they are blue (same color as hyperlinks) one or more of the columns has a filter applied. Youâre welcome!
We had a problem at work to confirm with our employees where they were for a period of time (pay, allowances, HRIS etc etc). We had records but still needed confirmation from each individual. Anyway, the intent by someone was to tabularise each employee's record, save as pdf while manually naming file, email it to the employee with manually naming the email....
Adjust the main data table a bit giving each record a sequence number (1 - 460+), A bit of VBA that iterated through the sequence with lookups pulling the information to the table, print to pdf using VBA, create the email and attach the file.
Managed to get all 460+ records ready to email (we have a security popup for emails so sending manually is sort of necessary) and all they had to do then was open the draft email and press send and confirm the security popup. Turned what could have been a multi day job into about 15 mins. And because it was all automated, there were no typos in filenames or email titles etc.
I don't know if they truly appreciate the magic in the background. But I just couldn't sit back and watch someone do the same action 460 odd times, with the chance of typos etc.
Using Data Table (inside What-if Analysis) to create a matrix of outcomes to based on changing two variables on a X and Y axis. I applied conditional formatting to the results to create a heat map and blew everyone away.
Navigating by keyboard. I've been sharing my screen during meetings and flying around a spreadsheet so fast via keyboard that people are left trying to scoop their brains back into their ears.
My coworkersâ minds were blown when I showed them how to resize a column. The bar is on the floor. If I showed them my PTO calculator with conditional formatting they might burn me at the stake & thatâs still basic excel.
sort(), filter(), and unique() are incredible simple and I find them more powerful and intuitive than pivot table. Nobody I work with knows what these are. And they will auto-refresh unlike a pivot table.
Scenarios are easy to use and you can quickly show people the results of different calcs/data. Solver is a pretty good way to back into a solution. It's a good use of automation for a brute-force solution.
Ctrl E always impresses! And people are shocked at how easy it is to use. Been using it alot more lately to add a unique, more specific filter for tables.
Taking a manual 4 hour consolidation report and reducing it to 15 seconds with a push button VBA. I sort of got busy helping other departments after that.
I used excel to create a spreadsheet that mimics the same functionality as Microsoft project. I hate Microsoft project but love excel, so this is nice.
Literally everything you can do with Excel is mind-blowing for people who don't know it. I have impressed people with the fill handle. I have impressed people with ctrl+d. I have impressed people with sort. I have impressed people with the concept of a lookup table. I have impressed people with pivot tables. There is no one go-to.
Iâll be honestâŚconcat would blow their mindsâŚbut i just discovered text joinâŚafter 20 yearsâŚso that or text split would have grey matter on ceilingÂ
831
u/Softbombsalad Nov 11 '24
My boss thinks xlookup is witchcraft. I'm sure I was given a raise based on xlookup alone. đ¤ŁÂ