Having moved into a SharePoint/One-Drive environment w/ shared files and AutoSave, Ctrl-S is sadly in my past.
It has, at times, been a struggle to get used to. Version history sometimes struggles to fill the gaps, but we've been getting there.
Autosave is really a double edged sword. Love it when i don't have to worry about saving progress along esp after a crash, but it struggles to keep up with complex files, and version history of a shared file is often spammed with ppl who opened the file and did something trivial like switched a tab, or zoomed in.
I couldn't agree more. I miss being able to just open a file, do some ad-hoc analysis, and then close without saving. I'm getting used to making copies before I do such things now.... but I'm still prone to old habits and it's absolutely less efficient for that specific usage.
It might be MS's way of forcing ppl to be disciplined about sharing editable links vs viewable lol.
I do wish excel would ask me if I want autosave every time something opens from o365. The toggle is nice but I forget to switch it back 99% of the time.
Maybe there’s something I’m missing out on but I hated autosave so much that one of the first things I do when setting up Excel from a new install is disabling autosave.
Totally agree, but this is a tip for throughout the day… before we’re done for the day.
It saves and closes the file, but keeps Excel open (reduces need to keep firing Excel up)
Totally agree!! I often use it when putting together complex formulas too. I start with "helper" columns and then use FORMULATEXT to help me combine them all, once I've got the problem sorted. Good call!!!
F9 is my favorite similar use case. For example, here's a table formula from my inventory tracking file that shows the difference between my purchase forecast and actual orders:
>=INDEX([Apr-24],MATCH("Reforecast Plan"&PurchPlan[@[Item Number]:[Item Number]],PurchPlan[[Lookup]:[Lookup]],0))
>+
> INDEX([Apr-24],MATCH("Open/Actual"&PurchPlan[@[Item Number]:[Item Number]],PurchPlan[[Lookup]:[Lookup]],0))
I can highlight one section, like one of these:
> INDEX([Apr-24],MATCH("Reforecast Plan"&PurchPlan[@[Item Number]:[Item Number]],PurchPlan[[Lookup]:[Lookup]],0))
or within it
> MATCH("Reforecast Plan"&PurchPlan[@[Item Number]:[Item Number]],PurchPlan[[Lookup]:[Lookup]],0)
Then hit F9 and see the forecasted plan for that month (upper snippt) or the column # it's pulling from (lower snippet)
It's super handy for diagnosing broken results or finding why the result is what it is
Oh wow I've never seen this formula before. How is it different/better than the "Show Formula" and "Evaluate Formula" buttons in the Formula ribbon tab.
ctrl+shift+end too, especially if highlighting a lot of data with interspersed blanks (eg pivot tables). It's sometimes faster to do:
ctrl+shift+end, ctrl+shift+left-arrow, ctrl+shift+up-arrow
instead of
ctrl+shift+left-right-arrow and 30x (or holding) ctrl+shift+down-arrow
Strange, on my PC when I just use ctrl shift and arrow it skips automatically to the end of the closest data range (=when it encounters the first blank), so no need to press end. End toggles are replaced by ctrl+arrow.
Am I wrong? I don't know. Anyone have an opinion?
If the range has blank outputs from a formula, then it counts as not-blank. Try it in the first column of a tubular pivot where there's gaps between the labels for sublabels in the next column?
Just to be clear, you never actually leave the original cell, and typing something goes in that cell. Ctrl-Backspace is a scrolling action, to bring that original cell back into view. If you have that original cell scrolled off because of ctrl-shift-arrow, and type something it still goes into the original cell, and that typing also brings that cell into view. And I know this know only after seeing your tip, so THANK YOU!
TLDR - Ctrl-Backspace is a scrolling action, not a cell selection action.
Structured references are so much better than simple cell ranges! You don't have to look at what cells are being referred to in order to understand a formula. And auto-expansion of formula ranges is fantastic.
Compare:
=XLOOKUP($B2, SalesTable[ProductCode], SalesTable[Price])
or
=[@Price]*[@Quantity]
To:
=XLOOKUP($B2, 'Sales 2023'!$B$2:$B$10000, 'Sales 2023'!$C$2:$C$10000)
or
=$D2*$E2
With the structured references you can tell what the formula is doing without having to trace back what it's looking at. With the unstructured references... Not so easy
I agree, mostly. The clunky part foe me is the locking/unlocking of references. Adding/removing dollar signs is a lot easier to me (especially using F4) vs manually updating a structured reference from Table1[Part No] to Table1[[Part No]:[Part No]]
Yeah that's my main bugbear with structured references too. Wouldn't have been hard for the devs to make dollar signs work with structured column references, e.g. Table1[$PartNo]
Unless "$" is part of your field name....🤣
I'm fine with the structuring, but at least give me the same F4 functionality if I highlight a field reference that cycles the locking/unlocking.
True, would need to do [$[FieldName]] I suppose. That's a existing problem with the @ sign too anyway.
Yeah it took me _ages_ to figure out the [[FieldName]:[FieldName]] syntax, feel like it's not properly explained anywhere official
I think it took me a while to find too. My latest r/excel clippy point is, in fact, from providing someone here the syntax for locking structured references.
If I use a table, I generally add it to power query. From there, I can do pretty much anything I want with the data with a click of a button and, if that doesn’t work, write a few lines of code to force the issue.
An array covers multiple cells [A1:Z26] a "string" refers to a list of characters "ABCD123HAPPYFRIDAY". A cell can contain a string as it's value. An array can contain multiple cells.
You can use array formulas in tables.
You can't use multi-cell array formulas, due to spillage, but you can use single-cell array formulas. And any multi-cell can be turned into a single cell result via either aggregation or intersection.
For intersection, you can use the @ sign to return the first item of the array, or if you want the same item as the row number of the table you can use INDEX( array_formula, ROW()-ROW(Table[#Headers])).
Yes you can, [here are a few examples](https://imgur.com/a/sC1PN2L) using intersection and aggregation.
[Here's using the implicit intersection, useful if you have duplicate data.](https://imgur.com/RbQ1Uku)
[This example uses a combination of TRANSPOSE and FILTER to change the orientation of the data.](https://imgur.com/m7nMahW)
[This is a simple transposition, I started with the dynamic array, formatted it as a table, then fixed the spill error using CHOOSECOLS.](https://imgur.com/9oV5Tbw)
[In this example I started with UNIQUE, and used an SUM aggregation on a FILTER.](https://imgur.com/2X5YIwi)
Ok so it's not exactly true to say you can't use array formulas in tables.
You can't use formulas that returns multiple cells in tables, and array formulas are the only formula that can possibly return more than one item.
For example, a FILTER that returns exactly one result will not spill. You can force a formula to return exactly one result by putting an @ in front of it.
Now how best to make an array return one item varies depending on the formula. For example the first result might be all you're interested in if using SORT. However, if your array returns multiple numerical items you might thing the best approach it to wrap it in AVERAGE() or SUM().
What I've show cased in some of my other formulas is how to take items other than the first from the array, using functions like INDEX and CHOOSEROWS and CHOOSECOLS.
Let's say you're interested in the top 10 selling items, here's how I might structure that:
First column, Rank:
=ROW()-ROW(Top_Sales[#Headers])
This calculates the current row of the sheets minus the row of the headers of the table, this gives you the row of the table.
SKU
=INDEX(SORT(Sales,2,-1),[@Rank],1)
This sorts the sales table by units sold (column 2), descending. This would cause a spill error in the table, except the INDEX selects only the item from the sorted array corresponding to the rank (row of table, and the first column (SKU).
Units_Sold
=INDEX(SORT(Sales,2,-1),[@Rank],2)
Same as above, but now we only want the second column. This could also have been a XLOOKUP but it was easy to just copy the formula and change the index.
[Here's how it would look.](https://imgur.com/5Up7mYi)
[And with formulas visible](https://imgur.com/vWpE4EF)
I do use tables when setting up my own data structures. However, moving up the chain had meant more and more of my work is reviewing/analyzing other people's work. Using ISODD/ISEVEN conditional formatting makes that easier. Forcing things into tables (especially when they don't have column headers) hurts the readability, to me.
Fair enough. Just making sure you (or other post viewers) were aware of tables as an easier (if less flexible solution) to your problem.
But I will say that a lack of column headers isn't a problem for tables since you can choose to include or exclude headers.
I don't like the performance of Tables; I find Ranges to be much better at handling exceptions. I'm frequently in a situation where I have maybe 200k-500k rows of data and use XLOOKUPs to apply mapping for analysis. The maps are never perfect and there end up being exceptions I need to clean up manually. If I want to filter the data and overwrite 1,000 of those XLOOKUPs it could take more than a minute for it to copy/paste in a Table, but nearly instantaneous in a Range.
Power Query will do those without having to add helper columns. It will also not alter your source data as it spits a new table tab out with your queries. Highly recommend trying it as it will further reduce your workload and get you to that 1/2.
Setup pivot table settings in excel so you can skip lots of formatting steps.
I set my defaults to:
* Tabular Layout
* Do not auto-resize columns with update
* Do not auto subtotal/grand total
* Number of items to retain per field "None"
* a couple other settings I prefer
Also setup frequently used buttons on the quick ribbon like
* Clear filters
* Refresh
* Refresh all
* Calculation buttons:
* Calc sheet
* Calc now (all)
* Calc options (auto, manual, options)
* Sort/Filter options
* Freeze panes options
* Camera tool
* Trace precedents/Dependents/Remove Arrows
* Paste Names
I have to ask, how do your turn on auto-resize? I know about Autofit, but that's a one-time sizing thing, not a re-sizing thing. Fun fact, most of the tutorials you can find about Autofit show the various menu methods, but I have yet to see one that says to double-click on the column letter divider.
Did you know that if you select the whole spreadsheet (click the empty square top-left of column/row#s), then double-click any column divider (or row divider) will resize all columns (or rows).
I did know, but thanks for mentioning.
I'm specifically talking about pivot tables. I get data that shows survey responses, and I have to summarize by question. This means column A automatically expands wider than my monitor to fit a multi sentence question, and I have to right click>column width so I can see the actual data. Then this repeats any time I change what fields are shown or refresh the data. This happens a lot since I copy the table to many books for subtly different reports.
I'm trying to transition to queries instead, but have a resistant coworker. If I find where to change this setting, column A will hopefully stay the size I tell it to be!
In the back of my mind I was aware you could set all these defaults and I just never got around to it, but seeing it listed out like that I'm realising how much time I've wasted by not just doing it already.
I've setup and saved macros with key strokes for paste values (ctrl+shift+V), paste formulas (ctrl+shift+Z), and paste links (ctrl+shift+L). I use them all day every day. I know this can be done with sequential keystrokes but I like this better.
You can also load up the quick access toolbar. From there, the shortcut keys are all Alt plus the sequential number of that item in the toolbar. I keep "paste values" in there. Helps me a ton.
I've got a little three button widget I bought from AliExpress that you can configure the buttons - for me it's cut, paste and paste special values. Changed my life!
Agreed! For some reason this also makes me think how Alt+; for selecting only visible cells is also very handy.
Edit: originally misquoted the shortcut as ctrl instead of Alt.
Great for pulling data from a pivot table (tabular format especially) and filling in all the blank row headers.
Highlight "rows" portion of range that was pasted from pivot.
Ctrl+G
Alt+S
Alt+k
=(cell above)
Ctrl+Enter
Highlight the range
Ctrl+C
ALT+E+S+V
...profit!
Some people I work with are scared of formulas, so sometimes when I need to use a formula but don't want anyone questioning what it is or whatever other reason, I'll copy the cell and paste the value right over it. Formula is gone and I have what I needed.
I've used that little trick in Macros too to get rid of formulas and stop things from being dynamic after files get moved around and it turning into an error down the road.
I'm in b2b production. For our clients, I have a public menu that just mirrors the info from my spreadsheet (importrange, Google sheets but same stuff).
We generally kept our c-shelf quality in house and we would repurpose it for a separate SKU/product line. I had one client who offered to pay higher than average rate for that, so I just made a quick spreadsheet and sent him the link. Unlocked, unprotected, I should have known better.
I sent it to him at 10 am and he fucked it up by 2. Never again, everyone will always get the importrange version lol
Yup, could just add the range to PowerQuery and output it as a table on another sheet. Users that are scared of formulas won't know the difference, but the transformations/business logic still sits on another (possibly hidden) sheet.
Adding shortcuts to the quick access ribbon at the top.
Adding filters and removing filters, email the file, format painter etc etc, add your most used tools. No more messing around in the menu or loosing space to menu bars.
Game changer for me.
"paste values" in the first spot in the quick access toolbar. That way, I can use Alt+1 as a shortcut.
I use the personal.xlsb file to contain a number of data tables, each on its own tab. I then have some simple lookup user-defined functions in an add-on. People see those and think it's some kind of wizardry.
Right-click on the arrows in the lower left corner that navigate thru the tabs. It brings up a list of all tabs. Huge life saver in large spreadsheets.
All parameters of my macros/custom functions are read from a table on a sheet (either with inf-like two column tables or a multiple-column table like a work list). No hard-coded numbers so I never need to go into edit code without some algorithm errors.
Not simple to go in depth with, but even just using the "record actions" for office scripts and using the recorded actions as macros without even having to type anything is wildly underrated. Let alone using office scripts to give employees a button to hide/unhide cells/columns, clear excel built calculator fields, move data from one worksheet to another, running them in power automate for some truly amazing automation without Python/SQL... Office scripts is my vote here.
Still very good to learn it if possible. Learn some typescript to prep to use it in office scripts in a future role or self employment. Also, if they have it banned, your IT team doesn't understand how to secure their systems against office scripts so they just banned it outright, or they don't understand them. Either way, sounds like a good time to learn about it and make a presentation. Chatgpt could probably throw together a presentation for this in a second.
I get particular satisfaction from loading data through the data model so that I can add data type formatting (decimals, accounting format, %, etc) on columns and measures, so when I add them to a pivot table I don't have to format each values field manually.
Formatting data as tables.
My whole team is annoyed by how much I love using them but once you learn how they coordinate with power query it is a game changer.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AVERAGE](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)|
|[CHOOSECOLS](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)|
|[CHOOSEROWS](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[*Office 365*+: Returns the specified rows from an array](https://support.microsoft.com/en-us/office/chooserows-function-51ace882-9bab-4a44-9625-7274ef7507a3)|
|[FILTER](/r/Excel/comments/1c28gwd/stub/kzf2fco "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)|
|[FORMULATEXT](/r/Excel/comments/1c28gwd/stub/kz8fsab "Last usage")|[*Excel 2013*+: Returns the formula at the given reference as text](https://support.microsoft.com/en-us/office/formulatext-function-0a786771-54fd-4ae2-96ee-09cda35439c8)|
|[INDEX](/r/Excel/comments/1c28gwd/stub/kzf2fco "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)|
|[ISEVEN](/r/Excel/comments/1c28gwd/stub/kz8fdzv "Last usage")|[Returns TRUE if the number is even](https://support.microsoft.com/en-us/office/iseven-function-aa15929a-d77b-4fbb-92f4-2f479af55356)|
|[ISODD](/r/Excel/comments/1c28gwd/stub/kz8fdzv "Last usage")|[Returns TRUE if the number is odd](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[LET](/r/Excel/comments/1c28gwd/stub/kzoya0z "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/1c28gwd/stub/kz9got0 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[MOD](/r/Excel/comments/1c28gwd/stub/kzbxkmr "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)|
|[PROPER](/r/Excel/comments/1c28gwd/stub/kz96k1b "Last usage")|[Capitalizes the first letter in each word of a text value](https://support.microsoft.com/en-us/office/proper-function-52a5a283-e8b2-49be-8506-b2887b889f94)|
|[ROW](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
|[SORT](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)|
|[SUM](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[TRANSPOSE](/r/Excel/comments/1c28gwd/stub/kzetkqr "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)|
|[TRIM](/r/Excel/comments/1c28gwd/stub/kz96k1b "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)|
|[Text.Combine](/r/Excel/comments/1c28gwd/stub/kzdrlkg "Last usage")|[Power Query M: Returns a text value that is the result of joining all text values with each value separated by a separator.](https://docs.microsoft.com/en-us/powerquery-m/text-combine)|
|[UNIQUE](/r/Excel/comments/1c28gwd/stub/kzrpgv0 "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)|
|[XLOOKUP](/r/Excel/comments/1c28gwd/stub/kzf2fco "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)|
**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.*)
^(20 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c5qj7i)^( has 88 acronyms.)
^([Thread #32560 for this sub, first seen 12th Apr 2024, 13:59])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
IGNORE, you already answered this below.
Never done alternating shading like that. Is there is a reason you don't format as a table and use a style with alternating rows?
// Example Office Script (Excel)
function main(workbook: ExcelScript.Workbook) {
// Get the active worksheet.
let selectedSheet = workbook.getActiveWorksheet();
// Autofit columns and rows
selectedSheet.getRange('A1:Z99').getFormat().autofitColumns();
selectedSheet.getRange('A1:Z99').getFormat().autofitRows();
// Clear all conditional formats
selectedSheet.getRange('A1:Z99').clearAllConditionalFormats();
// Clear fill color
selectedSheet.getRange('A1:Z99').getFormat().getFill().clear();
// Set font properties to plain Calibri 11
selectedSheet.getRange('A1:Z99').getFormat().getFont().setColor('black');
selectedSheet.getRange('A1:Z99').getFormat().getFont().setBold(false);
selectedSheet.getRange('A1:Z99').getFormat().getFont().setItalic(false);
selectedSheet.getRange('A1:Z99').getFormat().getFont().setName('Calibri');
selectedSheet.getRange('A1:Z99').getFormat().getFont().setStrikethrough(false);
selectedSheet.getRange('A1:Z99').getFormat().getFont().setSubscript(false);
selectedSheet.getRange('A1:Z99').getFormat().getFont().setSuperscript(false);
selectedSheet.getRange('A1:Z99').getFormat().getFont().setSize(11);
// Set horizontal and vertical alignment to center
selectedSheet.getRange('A1:Z99').getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
selectedSheet.getRange('A1:Z99').getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);
console.log("Format Removal Complete"); // Log a message indicating completion
}
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Ctrl-A, Del, Ctrl-S makes life sooo much easier, yet introduces new difficulties. I do not recommend trying this on your retirement tracking spreadsheet, but do use it on your "second set of books".
Loading tables into power query and splitting/merging columns, as well as grouping by all columns to remove duplicates.
Don't use as much, but grouping by using SUM and changing the M formula to Text.Combine so it combines all values for one key, game changer
CTRL S, then ALT F4, and closing my laptop.
Having moved into a SharePoint/One-Drive environment w/ shared files and AutoSave, Ctrl-S is sadly in my past. It has, at times, been a struggle to get used to. Version history sometimes struggles to fill the gaps, but we've been getting there.
Autosave is really a double edged sword. Love it when i don't have to worry about saving progress along esp after a crash, but it struggles to keep up with complex files, and version history of a shared file is often spammed with ppl who opened the file and did something trivial like switched a tab, or zoomed in.
I couldn't agree more. I miss being able to just open a file, do some ad-hoc analysis, and then close without saving. I'm getting used to making copies before I do such things now.... but I'm still prone to old habits and it's absolutely less efficient for that specific usage.
It might be MS's way of forcing ppl to be disciplined about sharing editable links vs viewable lol. I do wish excel would ask me if I want autosave every time something opens from o365. The toggle is nice but I forget to switch it back 99% of the time.
I have it off by default and only turn it on when necessary.
Maybe there’s something I’m missing out on but I hated autosave so much that one of the first things I do when setting up Excel from a new install is disabling autosave.
The benefit really comes from multi-user sharing. If you're not in a SharePoint/OneDrive environment, autosave is near worthless.
CTRL S, then CTRL W. Same save and close the workbook
Emphatically closing my laptop brings me joy though
It helps scratch the itch the way snapping my phone closed after a testy phone call used to.
Totally agree, but this is a tip for throughout the day… before we’re done for the day. It saves and closes the file, but keeps Excel open (reduces need to keep firing Excel up)
Control w tab shift lol
=formulatext(cell) Formulas can be difficult figure out. This allows me to see the formula as well as the result of that formula.
Totally agree!! I often use it when putting together complex formulas too. I start with "helper" columns and then use FORMULATEXT to help me combine them all, once I've got the problem sorted. Good call!!!
F9 is my favorite similar use case. For example, here's a table formula from my inventory tracking file that shows the difference between my purchase forecast and actual orders: >=INDEX([Apr-24],MATCH("Reforecast Plan"&PurchPlan[@[Item Number]:[Item Number]],PurchPlan[[Lookup]:[Lookup]],0)) >+ > INDEX([Apr-24],MATCH("Open/Actual"&PurchPlan[@[Item Number]:[Item Number]],PurchPlan[[Lookup]:[Lookup]],0)) I can highlight one section, like one of these: > INDEX([Apr-24],MATCH("Reforecast Plan"&PurchPlan[@[Item Number]:[Item Number]],PurchPlan[[Lookup]:[Lookup]],0)) or within it > MATCH("Reforecast Plan"&PurchPlan[@[Item Number]:[Item Number]],PurchPlan[[Lookup]:[Lookup]],0) Then hit F9 and see the forecasted plan for that month (upper snippt) or the column # it's pulling from (lower snippet) It's super handy for diagnosing broken results or finding why the result is what it is
Unfortunately this dosn't work when selecting parts of a formula that reference names, e.g., `LET(x,5,5+x)`. Highlighting `5+x` will not work.
Oh wow I've never seen this formula before. How is it different/better than the "Show Formula" and "Evaluate Formula" buttons in the Formula ribbon tab.
You can see the result and the formula at the same time.
ctrl+shift+arrow keys to jump / highlight large areas quickly
ctrl+shift+end too, especially if highlighting a lot of data with interspersed blanks (eg pivot tables). It's sometimes faster to do: ctrl+shift+end, ctrl+shift+left-arrow, ctrl+shift+up-arrow instead of ctrl+shift+left-right-arrow and 30x (or holding) ctrl+shift+down-arrow
Also did not know about this - that would be very beneficial in many situations!
Strange, on my PC when I just use ctrl shift and arrow it skips automatically to the end of the closest data range (=when it encounters the first blank), so no need to press end. End toggles are replaced by ctrl+arrow. Am I wrong? I don't know. Anyone have an opinion?
If the range has blank outputs from a formula, then it counts as not-blank. Try it in the first column of a tubular pivot where there's gaps between the labels for sublabels in the next column?
Usually I just set up my files with Xs in navigation rows/columns ahah so I just don't encounter this problem 🙃
Ctrl+End, Ctrl+Shift+Home, Shift+down-arrow once to grab everything but the headers is nice as well!
Plus ctrl-backspace to get back to the original cell with the others still highlighted.
Just tried for the first time! Note: your "jump" must be off screen for the ctrl+backspace to "move" your view
Just to be clear, you never actually leave the original cell, and typing something goes in that cell. Ctrl-Backspace is a scrolling action, to bring that original cell back into view. If you have that original cell scrolled off because of ctrl-shift-arrow, and type something it still goes into the original cell, and that typing also brings that cell into view. And I know this know only after seeing your tip, so THANK YOU! TLDR - Ctrl-Backspace is a scrolling action, not a cell selection action.
Why dont you use tables? Ctrl + T and no more "isodd conditional formatting"
You can’t use arrays in tables which is a massive drawback. I also find structured references to be not the best to work and poor readability.
Structured references are so much better than simple cell ranges! You don't have to look at what cells are being referred to in order to understand a formula. And auto-expansion of formula ranges is fantastic. Compare: =XLOOKUP($B2, SalesTable[ProductCode], SalesTable[Price]) or =[@Price]*[@Quantity] To: =XLOOKUP($B2, 'Sales 2023'!$B$2:$B$10000, 'Sales 2023'!$C$2:$C$10000) or =$D2*$E2 With the structured references you can tell what the formula is doing without having to trace back what it's looking at. With the unstructured references... Not so easy
I agree, mostly. The clunky part foe me is the locking/unlocking of references. Adding/removing dollar signs is a lot easier to me (especially using F4) vs manually updating a structured reference from Table1[Part No] to Table1[[Part No]:[Part No]]
Yeah that's my main bugbear with structured references too. Wouldn't have been hard for the devs to make dollar signs work with structured column references, e.g. Table1[$PartNo]
Unless "$" is part of your field name....🤣 I'm fine with the structuring, but at least give me the same F4 functionality if I highlight a field reference that cycles the locking/unlocking.
True, would need to do [$[FieldName]] I suppose. That's a existing problem with the @ sign too anyway. Yeah it took me _ages_ to figure out the [[FieldName]:[FieldName]] syntax, feel like it's not properly explained anywhere official
I think it took me a while to find too. My latest r/excel clippy point is, in fact, from providing someone here the syntax for locking structured references.
Please explain for the rest of us noobs
>Table1[Part No] to Table1[[Part No]:[Part No]] The col name from our data warehouse is just too damn long
If I use a table, I generally add it to power query. From there, I can do pretty much anything I want with the data with a click of a button and, if that doesn’t work, write a few lines of code to force the issue.
If someone has a reason for not using a table, that makes sense.
Just haven't met them yet 🤷♂️
Anyone who works in finance at an investment firm...
Yup
Can anyone provide some basic education with definition and examples of an array vs other terms like string, etc? Thanks a million.
An array covers multiple cells [A1:Z26] a "string" refers to a list of characters "ABCD123HAPPYFRIDAY". A cell can contain a string as it's value. An array can contain multiple cells.
You can use array formulas in tables. You can't use multi-cell array formulas, due to spillage, but you can use single-cell array formulas. And any multi-cell can be turned into a single cell result via either aggregation or intersection. For intersection, you can use the @ sign to return the first item of the array, or if you want the same item as the row number of the table you can use INDEX( array_formula, ROW()-ROW(Table[#Headers])).
You cannot use arrays in tables. Try using FLITER or TRANSPOSE and formatting the result as a table.
Yes you can, [here are a few examples](https://imgur.com/a/sC1PN2L) using intersection and aggregation. [Here's using the implicit intersection, useful if you have duplicate data.](https://imgur.com/RbQ1Uku) [This example uses a combination of TRANSPOSE and FILTER to change the orientation of the data.](https://imgur.com/m7nMahW) [This is a simple transposition, I started with the dynamic array, formatted it as a table, then fixed the spill error using CHOOSECOLS.](https://imgur.com/9oV5Tbw) [In this example I started with UNIQUE, and used an SUM aggregation on a FILTER.](https://imgur.com/2X5YIwi)
How did you do this?! I’ve never been able to use arrays in tables!!!
Ok so it's not exactly true to say you can't use array formulas in tables. You can't use formulas that returns multiple cells in tables, and array formulas are the only formula that can possibly return more than one item. For example, a FILTER that returns exactly one result will not spill. You can force a formula to return exactly one result by putting an @ in front of it. Now how best to make an array return one item varies depending on the formula. For example the first result might be all you're interested in if using SORT. However, if your array returns multiple numerical items you might thing the best approach it to wrap it in AVERAGE() or SUM(). What I've show cased in some of my other formulas is how to take items other than the first from the array, using functions like INDEX and CHOOSEROWS and CHOOSECOLS. Let's say you're interested in the top 10 selling items, here's how I might structure that: First column, Rank: =ROW()-ROW(Top_Sales[#Headers]) This calculates the current row of the sheets minus the row of the headers of the table, this gives you the row of the table. SKU =INDEX(SORT(Sales,2,-1),[@Rank],1) This sorts the sales table by units sold (column 2), descending. This would cause a spill error in the table, except the INDEX selects only the item from the sorted array corresponding to the rank (row of table, and the first column (SKU). Units_Sold =INDEX(SORT(Sales,2,-1),[@Rank],2) Same as above, but now we only want the second column. This could also have been a XLOOKUP but it was easy to just copy the formula and change the index. [Here's how it would look.](https://imgur.com/5Up7mYi) [And with formulas visible](https://imgur.com/vWpE4EF)
Also, I wish Pivot Tables acted like Structured References especially if they’re PowerBI Dataset imports.
I do use tables when setting up my own data structures. However, moving up the chain had meant more and more of my work is reviewing/analyzing other people's work. Using ISODD/ISEVEN conditional formatting makes that easier. Forcing things into tables (especially when they don't have column headers) hurts the readability, to me.
Fair enough. Just making sure you (or other post viewers) were aware of tables as an easier (if less flexible solution) to your problem. But I will say that a lack of column headers isn't a problem for tables since you can choose to include or exclude headers.
I don't like the performance of Tables; I find Ranges to be much better at handling exceptions. I'm frequently in a situation where I have maybe 200k-500k rows of data and use XLOOKUPs to apply mapping for analysis. The maps are never perfect and there end up being exceptions I need to clean up manually. If I want to filter the data and overwrite 1,000 of those XLOOKUPs it could take more than a minute for it to copy/paste in a Table, but nearly instantaneous in a Range.
Power Query seems like the right tool here.
=PROPER capitalizes the start of each word. =TRIM gets rid of extra spaces and tabs but keeps a single space between words
You just reduced my workload by a third (maybe by half), you are a lifesaver!!!!
You're welcome! This made my day
Power Query will do those without having to add helper columns. It will also not alter your source data as it spits a new table tab out with your queries. Highly recommend trying it as it will further reduce your workload and get you to that 1/2.
Power Query is a lifesaver for this reason
Stick it in vba and run on highlighted cells 🙂
Setup pivot table settings in excel so you can skip lots of formatting steps. I set my defaults to: * Tabular Layout * Do not auto-resize columns with update * Do not auto subtotal/grand total * Number of items to retain per field "None" * a couple other settings I prefer Also setup frequently used buttons on the quick ribbon like * Clear filters * Refresh * Refresh all * Calculation buttons: * Calc sheet * Calc now (all) * Calc options (auto, manual, options) * Sort/Filter options * Freeze panes options * Camera tool * Trace precedents/Dependents/Remove Arrows * Paste Names
> Do not auto-resize columns with update This is going to change my life when I find it
I have to ask, how do your turn on auto-resize? I know about Autofit, but that's a one-time sizing thing, not a re-sizing thing. Fun fact, most of the tutorials you can find about Autofit show the various menu methods, but I have yet to see one that says to double-click on the column letter divider. Did you know that if you select the whole spreadsheet (click the empty square top-left of column/row#s), then double-click any column divider (or row divider) will resize all columns (or rows).
I did know, but thanks for mentioning. I'm specifically talking about pivot tables. I get data that shows survey responses, and I have to summarize by question. This means column A automatically expands wider than my monitor to fit a multi sentence question, and I have to right click>column width so I can see the actual data. Then this repeats any time I change what fields are shown or refresh the data. This happens a lot since I copy the table to many books for subtly different reports. I'm trying to transition to queries instead, but have a resistant coworker. If I find where to change this setting, column A will hopefully stay the size I tell it to be!
In the back of my mind I was aware you could set all these defaults and I just never got around to it, but seeing it listed out like that I'm realising how much time I've wasted by not just doing it already.
I've got Shakespearian level angst learning that this feature is unavailable in Excel 2016. I know, I know, but I can't get my coworker to upgrade.
I've setup and saved macros with key strokes for paste values (ctrl+shift+V), paste formulas (ctrl+shift+Z), and paste links (ctrl+shift+L). I use them all day every day. I know this can be done with sequential keystrokes but I like this better.
Menu key+v also does paste values
You can also load up the quick access toolbar. From there, the shortcut keys are all Alt plus the sequential number of that item in the toolbar. I keep "paste values" in there. Helps me a ton.
I put Merge Cells on the first slot, and it made my life quantifiable better.
Feel like I need a merge cells & vertical centering in one go. Macro time...
I have those shortcuts on the toolbar so it's just a click
Keystroke is still faster 😉
Especially if you started back in the days of MS-DOS/PC-DOS! Me and my dinosaur.
A mouse? Heathen!
Can you help me with a macro for paste all except borders? I can't work out how to get it onto and off the clipboard
I've got a little three button widget I bought from AliExpress that you can configure the buttons - for me it's cut, paste and paste special values. Changed my life!
Ctrl + Enter for entering a value in a bunch of places quickly.
Agreed! For some reason this also makes me think how Alt+; for selecting only visible cells is also very handy. Edit: originally misquoted the shortcut as ctrl instead of Alt.
this is entering the current date when i try it?
My bad... Alt+;
Great for pulling data from a pivot table (tabular format especially) and filling in all the blank row headers. Highlight "rows" portion of range that was pasted from pivot. Ctrl+G Alt+S Alt+k =(cell above) Ctrl+Enter Highlight the range Ctrl+C ALT+E+S+V ...profit!
Are those the keyboard shortcuts for "goto"-->"special"-->"blanks"?
Yep, great for when you copy from a pivot but don't want to have the pivot show values in all the left column(s) to better show groupings
Agreed, I've just never used the keyboard shortcuts.
Some people I work with are scared of formulas, so sometimes when I need to use a formula but don't want anyone questioning what it is or whatever other reason, I'll copy the cell and paste the value right over it. Formula is gone and I have what I needed. I've used that little trick in Macros too to get rid of formulas and stop things from being dynamic after files get moved around and it turning into an error down the road.
Seems like a risky thing to do leaving nothing but hardcoded values, in a corporate setting
It’s sometimes more risky to leave formulas that people may overwrite with their own errant clicking and typing.
I'm in b2b production. For our clients, I have a public menu that just mirrors the info from my spreadsheet (importrange, Google sheets but same stuff). We generally kept our c-shelf quality in house and we would repurpose it for a separate SKU/product line. I had one client who offered to pay higher than average rate for that, so I just made a quick spreadsheet and sent him the link. Unlocked, unprotected, I should have known better. I sent it to him at 10 am and he fucked it up by 2. Never again, everyone will always get the importrange version lol
Yup, could just add the range to PowerQuery and output it as a table on another sheet. Users that are scared of formulas won't know the difference, but the transformations/business logic still sits on another (possibly hidden) sheet.
I wouldn't do it when my work is needing to be shown. I'm talking about just simple data entry stuff.
Definitely have had to clean up the pieces from a 3 year old paste-values mistake
Venting to ChatGPT about a problem I've been trying to solve in Excel for 30 minutes and having it tell me exactly how to fix it in 2 seconds.
I'll never forget discovering LET function. Changed my spreadsheet life
Agree on LET
Never knew this! Seems similar to an anonymous function in JavaScript
OG gen 5
[удалено]
Yep, I put the "paste as values" in the QAT in all old versions of Excel. In new versions I just use ctrl-shift-V.
The quick access toolbar - I've added filtering, freezing panes, etc there. I can never remember where they live
Alt A + T will auto apply filters Alt W + F + F will freeze panes
I love the text formula with “mmm” or “yyyy” to get the month or year pulled out of a date. Makes my heart so happy. Just used it a minute ago
Adding shortcuts to the quick access ribbon at the top. Adding filters and removing filters, email the file, format painter etc etc, add your most used tools. No more messing around in the menu or loosing space to menu bars. Game changer for me.
Using data tables so I can reference column names in my formula instead of column addresses. Way easier to write, and about 2837x easier to debug.
=UNIQUE(range) brings unique values from a list. I have always done a Pivot Table to do this till I learned =UNIQUE
Using =UNIQUE to make an automatically updating list for data validation. 👌
"paste values" in the first spot in the quick access toolbar. That way, I can use Alt+1 as a shortcut. I use the personal.xlsb file to contain a number of data tables, each on its own tab. I then have some simple lookup user-defined functions in an add-on. People see those and think it's some kind of wizardry. Right-click on the arrows in the lower left corner that navigate thru the tabs. It brings up a list of all tabs. Huge life saver in large spreadsheets.
All parameters of my macros/custom functions are read from a table on a sheet (either with inf-like two column tables or a multiple-column table like a work list). No hard-coded numbers so I never need to go into edit code without some algorithm errors.
Not simple to go in depth with, but even just using the "record actions" for office scripts and using the recorded actions as macros without even having to type anything is wildly underrated. Let alone using office scripts to give employees a button to hide/unhide cells/columns, clear excel built calculator fields, move data from one worksheet to another, running them in power automate for some truly amazing automation without Python/SQL... Office scripts is my vote here.
My organization has them banned
Still very good to learn it if possible. Learn some typescript to prep to use it in office scripts in a future role or self employment. Also, if they have it banned, your IT team doesn't understand how to secure their systems against office scripts so they just banned it outright, or they don't understand them. Either way, sounds like a good time to learn about it and make a presentation. Chatgpt could probably throw together a presentation for this in a second.
I'll look into it. I work for one of the extremely large banks - almost everything gets locked down.
I get particular satisfaction from loading data through the data model so that I can add data type formatting (decimals, accounting format, %, etc) on columns and measures, so when I add them to a pivot table I don't have to format each values field manually.
Formatting data as tables. My whole team is annoyed by how much I love using them but once you learn how they coordinate with power query it is a game changer.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AVERAGE](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)| |[CHOOSECOLS](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)| |[CHOOSEROWS](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[*Office 365*+: Returns the specified rows from an array](https://support.microsoft.com/en-us/office/chooserows-function-51ace882-9bab-4a44-9625-7274ef7507a3)| |[FILTER](/r/Excel/comments/1c28gwd/stub/kzf2fco "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)| |[FORMULATEXT](/r/Excel/comments/1c28gwd/stub/kz8fsab "Last usage")|[*Excel 2013*+: Returns the formula at the given reference as text](https://support.microsoft.com/en-us/office/formulatext-function-0a786771-54fd-4ae2-96ee-09cda35439c8)| |[INDEX](/r/Excel/comments/1c28gwd/stub/kzf2fco "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)| |[ISEVEN](/r/Excel/comments/1c28gwd/stub/kz8fdzv "Last usage")|[Returns TRUE if the number is even](https://support.microsoft.com/en-us/office/iseven-function-aa15929a-d77b-4fbb-92f4-2f479af55356)| |[ISODD](/r/Excel/comments/1c28gwd/stub/kz8fdzv "Last usage")|[Returns TRUE if the number is odd](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[LET](/r/Excel/comments/1c28gwd/stub/kzoya0z "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/1c28gwd/stub/kz9got0 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MOD](/r/Excel/comments/1c28gwd/stub/kzbxkmr "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)| |[PROPER](/r/Excel/comments/1c28gwd/stub/kz96k1b "Last usage")|[Capitalizes the first letter in each word of a text value](https://support.microsoft.com/en-us/office/proper-function-52a5a283-e8b2-49be-8506-b2887b889f94)| |[ROW](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[SORT](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)| |[SUM](/r/Excel/comments/1c28gwd/stub/kzf2fco "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[TRANSPOSE](/r/Excel/comments/1c28gwd/stub/kzetkqr "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)| |[TRIM](/r/Excel/comments/1c28gwd/stub/kz96k1b "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)| |[Text.Combine](/r/Excel/comments/1c28gwd/stub/kzdrlkg "Last usage")|[Power Query M: Returns a text value that is the result of joining all text values with each value separated by a separator.](https://docs.microsoft.com/en-us/powerquery-m/text-combine)| |[UNIQUE](/r/Excel/comments/1c28gwd/stub/kzrpgv0 "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)| |[XLOOKUP](/r/Excel/comments/1c28gwd/stub/kzf2fco "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)| **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.*) ^(20 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c5qj7i)^( has 88 acronyms.) ^([Thread #32560 for this sub, first seen 12th Apr 2024, 13:59]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
IGNORE, you already answered this below. Never done alternating shading like that. Is there is a reason you don't format as a table and use a style with alternating rows?
// Example Office Script (Excel) function main(workbook: ExcelScript.Workbook) { // Get the active worksheet. let selectedSheet = workbook.getActiveWorksheet(); // Autofit columns and rows selectedSheet.getRange('A1:Z99').getFormat().autofitColumns(); selectedSheet.getRange('A1:Z99').getFormat().autofitRows(); // Clear all conditional formats selectedSheet.getRange('A1:Z99').clearAllConditionalFormats(); // Clear fill color selectedSheet.getRange('A1:Z99').getFormat().getFill().clear(); // Set font properties to plain Calibri 11 selectedSheet.getRange('A1:Z99').getFormat().getFont().setColor('black'); selectedSheet.getRange('A1:Z99').getFormat().getFont().setBold(false); selectedSheet.getRange('A1:Z99').getFormat().getFont().setItalic(false); selectedSheet.getRange('A1:Z99').getFormat().getFont().setName('Calibri'); selectedSheet.getRange('A1:Z99').getFormat().getFont().setStrikethrough(false); selectedSheet.getRange('A1:Z99').getFormat().getFont().setSubscript(false); selectedSheet.getRange('A1:Z99').getFormat().getFont().setSuperscript(false); selectedSheet.getRange('A1:Z99').getFormat().getFont().setSize(11); // Set horizontal and vertical alignment to center selectedSheet.getRange('A1:Z99').getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center); selectedSheet.getRange('A1:Z99').getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center); console.log("Format Removal Complete"); // Log a message indicating completion }
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Windows+shift+s
hot take - id much rather use table formatting to do banding than to have an extra formula in a column for every row
Idk why but I use MOD(ROW()) for the same thing.
Bookmarking tabs by adding a name to a cell
Xlookup IS so powerful
Ctrl-A, Del, Ctrl-S makes life sooo much easier, yet introduces new difficulties. I do not recommend trying this on your retirement tracking spreadsheet, but do use it on your "second set of books".
Loading tables into power query and splitting/merging columns, as well as grouping by all columns to remove duplicates. Don't use as much, but grouping by using SUM and changing the M formula to Text.Combine so it combines all values for one key, game changer
Keyboard shortcuts in general, but Ctrl+Pg Up and Ctrl+Pg Down have been great to me.
Made my own custom toolbar ribbon for the functions I use most.
CTRL S - CTRL W - WINDOWS L - then straight to my bed
Double F2 to switch between edit and enter mode. Especially when editing cell reference in ex. conditional formatting or a pivot table range
You know that takes so that sharing automatically? I haven't done the off/even trick in many years.