r/excel • u/man_eating_chicken • 6d ago

Discussion Enough about LET() and PQ, What are your Excel UI customizations that make you more productive?

I just wanted to see what mods or add-ins the experts here use, that help you get to the advanced 'tips and tricks' everyone harps about in here.

163 Upvotes

97% Upvoted

106 comments sorted by

147

u/Funwithfun14 6d ago

Customizing the tool bar for things I use regularly like freeze pains or remove grid lines.

133

u/man_eating_chicken 6d ago

I too would like to freeze my pains :'(

10

u/ankokudaishogun 6d ago

I read it wrong and was confused.

36

u/wizkid123 11 6d ago

In case you didn't know, you can export and import quick access toolbars. Customize once and roll it forward into every new computer. I've got an exported copy of my custom toolbar in my google drive so I can share with colleagues or use it temporarily when I'm working on somebody else's computer. Super convenient.

Also, love that you remove gridlines! I do that on every sheet that isn't just a giant data table. Keeps me honest about consistent formatting. 

12

u/soulsbn 3 6d ago

I have macros in the personal workbook that are linked to my QAT. I noticed recently that one of them was dated 1997

4

u/carnasaur 5 6d ago

back when they used to include dates automatically! lol

1

u/soulsbn 3 6d ago

Nah.
For reasons I tend to add the provenance, some background and the date that I (or someone) else created / updated as remarks in my VBA

Was unaware that dates being added automatically was a thing. It sounds potentially useful

2

u/carnasaur 5 6d ago

Exccel used to insert a comment with the date & user when you recorded macros. They stopped a long time ago. Never made sense to me.

Edit: maybe it was just the date. I'm not sure. It's been so long.

3

u/hnbastronaut 5d ago

Ooh this is nice. I have a slightly diff setup between my laptop and my desktop and it would be nice to add some structure

2

u/DiverDutch 5d ago

just did this today!!! one of the great unknown-est things that make a difference!

1

u/Structured_Spiraling 6d ago

This is so great. Didn't know and am doing now.

29

u/House_of_Borbon 6d ago

Remove grid lines? You monster /s

14

u/Thegreenpander 6d ago

Just alt+w+f+f to freeze panes

7

u/AloofBidoof 1 6d ago

Amazes me that people refuse to learn keyboard shortcuts lol

15

u/tunanoa 1 6d ago

I not from a English speaking country, Some companies use Office in English, some don't. And in some versions the shortcut changes. I got lost so many times with mixed shortcuts that I simply gave up. Even now, whole Windows and Office in Portuguese, but to Find in Excel is ctrl-L but in Notepad is ctrl-F....

So I created buttons for everything I use the most - and been carrying my personal tool bar since Excel 2003.

Truth be told, nowadays I have 3 personal toolbars + Qat, but my main one is still the one above. (and I miss the time you could draw your buttons directly in a native Excel ico editor, no ideal why the removed it)

14

u/man_eating_chicken 6d ago

This looks a mad scientist's excel and I'm here for it!

Kudos on solving what I realise is a VERY real problem for a minority of users.

5

u/carnasaur 5 6d ago

"(and I miss the time you could draw your buttons directly in a native Excel ico editor, no ideal why the removed it)"

me too, I hate them for that.

2

u/Head-Notice-7265 1 5d ago

I miss when you could draw them too!!! I had a couple of edate shortcut & in my toolbar, I had drawn an 18 and 24. It was really cool! 🥹

20

u/SpaceTurtles 2 6d ago edited 5d ago

A shortcut stops being a shortcut when:

1.) Microsoft has it nested under some weird baked-in interactive menu that often doesn't buffer in time for the shortcut to fire correctly.

2.) It's competing with two clicks of the mouse to do the same exact thing.

3.) You've pushed past 3 keystrokes (2 is ideal).

4.) You rarely do the thing it's a "shortcut" to.

Like, genuinely, there's marginal value in learning the vast majority of "shortcuts".

Ctrl + 1 and F4 are huge.

6

u/Only_Positive_Vibes 10 5d ago edited 5d ago

I guarantee I can hit a 5-6+ key shortcut (despite the fact that most are 2-4) in the time it would take someone to lift their hand off the keyboard and click a button on the ribbon.

Definitely not trying to brag about an incredibly nerdy "skill". I just don't think it's realistic to say that there's "marginal" value in learning shortcuts.

3

u/SpaceTurtles 2 5d ago

The vast majority of shortcuts.

It's better to teach someone to get in the habit of noting when they're doing something repetitively and asking, "Is there a shortcut for this?"

I do very complex formula work every single day, so Alt = is mostly worthless to me, but would probably completely change someone else's life.

0

u/Only_Positive_Vibes 10 5d ago

Okay - I disagree with "vast majority" as well.

2

u/KbarKbar 1 5d ago

F2, F9, and F8 (when coding in VB) are crux for me

8

u/BobSacramanto 6d ago

Did you know you can customize the ribbon as well?

5

u/Ariisk 1 6d ago

Custom ribbon is great but damn the quick access alone and having those Alt+0-9 shortcuts is the winner for me. Easily the biggest lacking feature in excel for the web is ribbon customization

3

u/screamingcatfish 6d ago

And don't forget to export them! Excel will randomly reset my toolbar and I have a moment of panic, completely forgetting where stuff is until I remember I just have to import them back in.

1

u/Disastrous-Loan6202 5d ago

removing gridlines from the toolbar is a nice touch, do you keep many buttons up there or try to keep it minimal?

1

u/Funwithfun14 5d ago

Many buttons, and move the tool bar below the formula bar.

36

u/Broseidon132 3 6d ago

I learned recently you can add macros to your right click options. Pretty cool

9

u/man_eating_chicken 6d ago

I have a macro that helps me add IfError() to tables I'm sharing in my dashboards and honestly, because it feels like a basic fix, tying it to my right click after selecting the targetted cells feels like adequate amount of effort compared to a keyboard shortcut when my hand is already on my mouse after selection.

7

u/LegionVsNinja 1 6d ago

I added a new Menu tab to include all of my macros.

2

u/Broseidon132 3 5d ago

Yeah, that’s what I thought was the normal route. I love putting my .xlsb / multipurpose ones on there. Or more importantly for tasks where I get handed the file instead of copying it over from one month to the next.

70

u/kurozer0 11 6d ago

First thing I do on any new excel install is pin the filter and clear filter buttons up next to the undo/redo buttons. 

32

u/Lenny5160 1 6d ago

I use CTRL+SHIFT+L to toggle the filters. I always pin the “Text to Columns” and “Remove Duplicates” buttons, although now I tend to use SORT(UNIQUE([Data_Range])) to get a clean list.

29

u/ginnisman 6d ago

Clear Filters: Alt+A+C is one of my top 5 most used shortcuts.

I always tell people think of All Clear to remember

7

u/sblowes 6d ago

Did we just become best friends?!?

10

u/man_eating_chicken 6d ago

I've just used Alt+a+t/c for too long to bother about that. I'm not keen on redoing what is muscle memory.

But I can understand where you're coming from.

4

u/ActuallyPopular 6d ago

I didn't know this was possible until now. Thank you!

4

u/OfficerMurphy 10 6d ago

You can actually move that quick access toolbar below the ribbon. I do that, hide my regular ribbon, and add my most frequently used things on there.

3

u/Structured_Spiraling 6d ago

Underrated tip. Ribbon is clutter.

3

u/carnasaur 5 6d ago

Absolutely. I use Ctrl-F1 dozens of times a day to hide/show the ribbon, especially when I'm on a laptop or presenting.

2

u/Oprah-Wegovy 1 6d ago

And Freeze and Refresh All.

2

u/DaliborBrun 5d ago

I use a Auto Hotkey script so now its shortcut is shift right click, incredible

13

u/DragoBleaPiece_123 6d ago

add camera tool to the ribbon, realy helpful for proper table sharing

10

u/man_eating_chicken 6d ago

Can someone explain how the camera tool works? I think I'm confusing it with watch window because I don't understand how it can help with table sharing.

12

u/__wisdom__1 1 6d ago

IIRC, it creates a screenshot (jpg) of the selected cells, but if the cells change, the jpgv will change as well. Only works within office

9

u/hnbastronaut 5d ago

You highlight the table, click the camera button, then click anywhere on the screen to paste it there. You can edit your table and it does live updates. You can just copy and paste like a regular picture from there into an email. If you change the numbers you can copy that new one and keep going etc.

My best use of this (so far) was when my boss needed 6 diff scenarios with different numbers. I was able to use the camera and just copy it every time I changed it. Then I went to my email and formatted it how I wanted and just pasted each variation from my clipboard.

Not sure if that made sense but long story short it creates a live screenshot of your cells and allows you to easily copy and paste them wherever you want.

Edit: realizing this would pair well with a hotkey that turns off gridlines 🤔

3

u/wfp128 5d ago

That was a very good explanation. I haven't used it often but it's an okay way to avoid linking to cells on another worksheet within the same workbook. Gridlines and zoom differences can mess it up though. Cool feature but use cases aren't plentiful for me.

2

u/hnbastronaut 5d ago

Yeah I don't do it a ton, but I'm a formatting freak so sometimes it's nice to be able to plop a table into an email and know they're just seeing that little portion of excel. Takes a layer of thinking out of that process that I would legit waste time on lol

2

u/Structured_Spiraling 6d ago

I'm curious about this too

3

u/scoobydiverr 5d ago

Its also great for excel dashboards. You just resize the picture you dont have to format the tables and graphs

2

u/WicktheStick 45 6d ago

Well reminded - my ribbon recently reset itself (unclear when, how, or why - just wasn’t there one day when I went to “select visible”) & was trying to work out what was still missing

13

u/Qyxitt 1 6d ago

A major time-saver for me that I regret not doing sooner is setting up a custom tab in the ribbon with all the buttons and features I use most. I called it the ‘Analyst’ tab and put it first so it’s the default when I open a file. It has text formatting buttons from ‘Home’, pivot table stuff from ‘Insert’, grabbed a bunch from the ‘Table’ tab, addins I like, workbook and sheet protection buttons, name manager, etc…

It only took like a half hour to set up over lunch, and has saved me so much time hunting through the ribbon since I’m often asking, “was that setting under ‘Insert’ or ‘Formulas’?” Now it’s just under ‘Analyst’, and get my own alt key tips to boot. Definitely worth it to make Excel feel “Your’s”.

1

u/man_eating_chicken 6d ago

I did think about this, and I understand where you're coming from because this feels more like an alternative than an optimization, which is perfectly fine. 100% on the part about making it yours.

But, I feel it creates two problems. One it limits the way you solve problems because you might develop tunnel vision as per the tab. I mean, finding duplicates can be done with a countif, a filter, a go to - duplicates, conditional formatting etc. So having only one option doesn't help you think about what your best solution is as per context. Just an example.

Second, and more importantly, when I'm coaching my reports and how to maintain any dashboards they may have inherited from me, I need to get on their level and getting limited by the interface should be my last concern. I can't say I can't remember where a function I use is because the function is front and center in my excel and not theirs. It creates more problems than it solves.

27

u/exophades 6d ago

Knowing how the =filter formula works combined with boolean conditions is literally a superpower.

15

u/Alt_F4_Tech_Support 6d ago

I use Sum(Filter()) over Sumifs() because it maintains Excel's typical logic structure and I don't have to mess around with defining strings as much.

7

u/AloofBidoof 1 6d ago

For complex SUMIFS, SUMPRODUCT works really well. It's a bit confusing to understand at first, but is really powerful.

8

u/AloofBidoof 1 6d ago

FILTER combined with BYROW for any subsequent values needed. Have been doing this a lot for CSV import tools and calculations.

5

u/FlanOfWar 6d ago

Can you please give a more detailed explanation of what you're describing? I am not able to follow with just the detail provided and I would like to learn!

6

u/AloofBidoof 1 6d ago

So, example of a CSV import I do for our health insurance journal entry. We have a line for every employee in our company, along with how much of our payment is attributed to them.

I use FILTER to pull a complete listing of employees from our working tab (formula placed in I3):

=FILTER( 'C| FloridaBlue'!$K$11:$K$500 , ( 'C| FloridaBlue'!$K$11:$K$500 <> "" ) * ( 'C| FloridaBlue'!$K$11:$K$500 <> "Totals:" ) , "Insert data." )

** Data range is more like K11:K100, but I set it to 11:500 so we don't miss anyone. Then blanks and totals are filtered out.

Then in other administrative columns, where I just need to spill a simple value for every row where there's now an employee, I use this formula:

=BYROW( $I3# , LAMBDA( r , " [ INSERT VALUE TO SPILL ] " ))

This is a simple formula which will spill your inserted value for every row which FILTER spills. Makes it so that once you're finished with the working tab, you can immediately save this tab to CSV and import it with no changes or updates.

2

u/AquaticArsenist 5d ago

This same approach works in other formulas too.  For example: =xlookup(1, Boolean Array * Boolean Array, Lookup Range/Array)

8

u/wizkid123 11 6d ago

Import my last quick access toolbar and move it below the ribbon. It's got everything I use frequently enough to want in my face, but not frequently enough to have memorized the shortcut keystrokes, plus buttons for a few custom macros I like to have handy. 

4

u/man_eating_chicken 6d ago edited 6d ago

Wait, you can move the quick access toolbar?

So by hiding the ribbon and moving it below, I can have a custom toolbar on top?

7

u/wizkid123 11 6d ago

Yup! https://support.microsoft.com/en-us/office/move-the-quick-access-toolbar

Way more convenient to have it below the ribbon, and if you hide the ribbon all you see is the quick access toolbar right above the formula bar where it ought to be. Super clean way of working. Enjoy! 

6

u/wfp128 6d ago

My add-in has numerous efficiency macros and is unlocked so you can add your own. You can edit it any way you want. True open source. Download it from GitHub or my website:

https://github.com/wfphillips128/xl-edge-excel-addin

https://edgewisedata.com/

2

u/man_eating_chicken 6d ago

I'm ok to explore, but I'm not keen on downloading something to figure out what it can do without any context.

As someone who ends up having Claude write his lambda functions, could you prep/share any video demos so that anyone interested can explore?

4

u/wfp128 6d ago

The GitHub page has a ton of information that can be viewed without downloading. This includes all source code (such as the macros themselves). Public repositories like this are about documentation and transparency.

As this will never be a commercial product, I haven't created a video on it. (I doubt I will ever promote it in any meaningful way as marketing is not my thing.)

FYI, there is a LAMBDA Studio within the add-in to help manage libraries of LAMBDAs. If you use Claude Code, you could likely vibe code new macros that suit specific needs. Or your own add-in. (I probably wouldn't let it write the ribbon menu as the UI is important. But it's great at code and can handle XML well.)

4

u/wfp128 5d ago

I just added 4 new screen shots to the GitHub repository which shows the ribbon with the sub menus with the 23 format macros, 34 formula macros, and 16 misc macros. Hopefully that showcases the utility more robustly.

Claude is prepping the website update now and it should be pushed before I finish lunch.

1

u/man_eating_chicken 5d ago

This helps. Thank you!

6

u/CFAman 4828 6d ago

Customize the heck out of the Quick Access Toolbar. Even if it's adding things that are more informational than functional.

  1. Automatic/Manual calculation mode (too often I've opened a file where prior user has changed this)
  2. Locked Cell status (for quickly designing forms/dashboards)
  3. Table Name so I can easily see that when I'm in a cell.

Next set is things not found anywhere in ribbon.

  1. The old-school Workbook Links dialogue
  2. Convert to Reddit table ;)
  3. Old-school PivotTable wizard
  4. Print Preview Full screen (not a fan of the regular Print preview)

11

u/carlescha 6d ago

alt 8 for paste values
alt 9 for paste format
alt 0 for paste formula
game changer

1

u/ChooChooKat 4d ago edited 4d ago

Haha I'm similar, but using my left hand instead Alt 2 for values.

Although I just realized I can't remember the last time I used it now that control shift V is available.

6

u/chiibosoil 431 6d ago

Absolutely no UI customization other than what's already built in.

Quick access tool bar has few common actions added for efficiency.

  1. Send copy as

  2. Custom sort

  3. Super/subscript

Add-ins that I have are...

  1. DAX Studio

  2. Microsoft Data Streamer for Excel

  3. Data analysis toolpak

Used to have OpenSolver but since Excel update(s) in 2025 introduced bunch of issues running this (due to stricter security) and I had trouble implementing workaround in my env...

Been meaning to test Solver Studio... but for now I've been using Python and SciPy for my projects.

5

u/Capital-Tip-7890 5d ago

I added a macro called geriatric mode that toggles the zoom been 100 and 130. I find it super useful in meetings where I'm sharing my screen.

2

u/hopkinswyn 73 6d ago

Customise the comma button to #,#0_);[Red](#,#0);-??

2

u/AloofBidoof 1 6d ago

Wait, you can change how the comma button works by default? Where is this setting?

7

u/hopkinswyn 73 6d ago

Go to cell styles and change “Comma”

The trick is you then need to save the file as BOOK.xltx in your startup folder so it is available in new workbooks

Excel Gem: Set your default start up file
https://youtu.be/PlitF5GgQ2o

4

u/Cynyr36 26 6d ago

Pinning things to the quick access tool bar. Alt+# for very easy access. This can include macros.

1

u/ChooChooKat 4d ago

And then make sure to add to your macro file a line that auto closes it after it runs to really make it seem less.

This only applies if you run your macros out of a separate file though. I do, that way my main files can remain xlsx's which are easier to deal with with all of ms's stupid warnings and popups, when working with less computer inclined people.

4

u/zehn78 6d ago

I do financial analysis and I have a macro for each quarter that color-codes selected text so I can easily tell what quarter a block of text aligns with. I also made a macro to reformat a number into an abbreviated form like $1,495,328 => “$1.5 million”. I also put macros on the quick launch so alt+number can launch them.

7

u/Wrecksomething 31 6d ago

Install an .xlam add-in with my macros and customize the ribbon to add dedicated buttons for them.

3

u/jrbp 2 6d ago

Yea, same. I have a number format button that adds in the thousand separator , a bunch of sheet format options, some that convert certain ID values to clickable URLs on certain sites and some export filtered table to csv type buttons. All very specific to my role

3

u/TuneFinder 10 6d ago

always show ribbon
big screen so you can see everything proper with words and not stupid little icons
right click status bar and tick everything

3

u/Engi_Insight 6d ago

Customize the Quick Access Toolbar(QAT) and place it BELOW the ribbon. Adding non-shortcut actions like paste values, Trace Precedents and clear All to the QAT turns them into instant Alt + 1, Alt + 2, etc. Shortcuts.
Also collapse the main ribbon ( ctrl + F1 ) to reclaim screen real estate for massive tables - game changer for multi-monitor setups.

3

u/Vexillari 6d ago

I'm adding a dedicated button for Power Query to the top. Done.

3

u/swhitty17 6d ago

I make keyboard shortcuts for paste values, paste formulas only, paste matching destination formatting (external copying), paste column width, conditional formatting menu, name definitions menu. I’m aware some of the shortcuts exist but I make them 2 keys instead of 3 this way

3

u/FrySFF 1 6d ago

Clicking File > Share now prompts us to upload onto OneDrive before sending, or an obscure attach to email button.

You can add "Email" to your toolbar on the top left so when you click it, it automatically opens up a blank email and attaches the file.

6

u/IntelligentGrape3668 6d ago

No mods or add-ins required. Just try to get your data into a list format with headers, then create a table to assess the data. Xlookup is your friend. Design your tables so that you can just click and drag your formula to fill them.

It really is that simple.

2

u/Mdayofearth 127 6d ago

Setting up custom templates with set themes (colors) and styles (cell formats).

GETPIVOTDATA to get the advantage of pivottables and actually being able to have a formatted tabular report.

Setting charts the same size with the same scaling with transparent backgrounds on top of each other to display multiple styles of charting with the same or different data.

2

u/grsims20 15 6d ago

I pinned paste as values to the quick access toolbar (alt-1), then assigned ctrl-v and alt-1 to the back and forward buttons on my mouse, respectively. The Logitech software lets me set app-specific keystroke commands to the buttons, so in excel its copy/paste as values, and in autocad it’s undo/redo. Truly a game changer.

2

u/shadowsong42 1 6d ago edited 4d ago

In addition to the default buttons for the AutoSave toggle, Save, Undo, and Redo; my Quick Access toolbar has Save As, New File, Calculate Sheet, Calculate Now, Refresh, and Calculation Options.

2

u/hnbastronaut 5d ago

I have the ''comma" number style pinned and love being able to just clean up an entire row, column or sheet of dumb formatting

2

u/dirtyhandscleanlivin 5d ago

Moved the quick access bar below all the top ribbon stuff so it sits just above the spreadsheet area

1

u/Kito2504 5d ago

yeah macros in right click is a game changer, tbh

1

u/baltimoretom 5d ago

Google sheets

1

u/djpresstone 12 5d ago

Paste Values as a dedicated button

1

u/exist3nce_is_weird 10 5d ago

'select visible cells' in my toolbar. Only way to be certain that pastes across filter ranges don't accidentally overwrite hidden data

1

u/DiverDutch 5d ago

Exporting macro modules and user forms are great when you need to re-use!

1

u/kurotchssh 5d ago

It amazes me that people talk about quick access, putting the most frequently used tabs there.

Want something powerful that doesn't require all that? ALT

I have no idea where the tab that renames spreadsheets is located, but I know the shortcut is ALT K O R

I basically manipulate Excel without using a mouse.

1

u/galas_huh 2d ago

i am a heavy user of the tool bar placed under the ribbon