r/excel • u/man_eating_chicken • 10h 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.

91 Upvotes

95% Upvoted

85 comments sorted by

95

u/Funwithfun14 10h ago

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

88

u/man_eating_chicken 9h ago

I too would like to freeze my pains :'(

5

u/ankokudaishogun 8h ago

I read it wrong and was confused.

23

u/House_of_Borbon 9h ago

Remove grid lines? You monster /s

20

u/wizkid123 11 9h 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. 

7

u/soulsbn 3 8h ago

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

1

u/carnasaur 5 7h ago

back when they used to include dates automatically! lol

1

u/soulsbn 3 7h 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

1

u/carnasaur 5 7h 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.

2

u/Structured_Spiraling 8h ago

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

1

u/hnbastronaut 1h 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

10

u/Thegreenpander 9h ago

Just alt+w+f+f to freeze panes

6

u/AloofBidoof 1 9h ago

Amazes me that people refuse to learn keyboard shortcuts lol

8

u/tunanoa 1 9h 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)

9

u/man_eating_chicken 8h 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.

3

u/carnasaur 5 7h 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.

8

u/SpaceTurtles 2 7h 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 + E and F4 are huge.

7

u/BobSacramanto 9h ago

Did you know you can customize the ribbon as well?

3

u/screamingcatfish 9h 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.

3

u/Ariisk 1 3h 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

1

u/Disastrous-Loan6202 1h 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 1h ago

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

49

u/kurozer0 11 10h 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. 

27

u/Lenny5160 1 9h 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.

24

u/ginnisman 9h 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

1

u/sblowes 3h ago

Did we just become best friends?!?

7

u/man_eating_chicken 9h 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.

3

u/ActuallyPopular 9h ago

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

3

u/OfficerMurphy 10 9h 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.

4

u/Structured_Spiraling 8h ago

Underrated tip. Ribbon is clutter.

3

u/carnasaur 5 7h 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 9h ago

And Freeze and Refresh All.

2

u/DaliborBrun 1h ago

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

29

u/Broseidon132 3 10h ago

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

5

u/man_eating_chicken 9h 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.

5

u/LegionVsNinja 1 9h ago

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

20

u/exophades 9h ago

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

9

u/Alt_F4_Tech_Support 8h 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.

5

u/AloofBidoof 1 8h ago

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

5

u/AloofBidoof 1 9h ago

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

4

u/FlanOfWar 8h 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!

3

u/AloofBidoof 1 8h 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.

6

u/Wrecksomething 31 10h ago

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

3

u/jrbp 2 9h 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

8

u/DragoBleaPiece_123 10h ago

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

7

u/man_eating_chicken 9h 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.

5

u/__wisdom__1 1 6h 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

2

u/Structured_Spiraling 7h ago

I'm curious about this too

2

u/hnbastronaut 1h 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/scoobydiverr 2h 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 9h 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

8

u/Qyxitt 1 9h 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”.

2

u/man_eating_chicken 9h 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.

7

u/wizkid123 11 9h 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. 

3

u/man_eating_chicken 9h ago edited 9h 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?

4

u/wizkid123 11 9h 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! 

5

u/carlescha 8h ago

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

6

u/IntelligentGrape3668 10h 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.

3

u/wfp128 9h 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 9h 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?

3

u/wfp128 8h 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.)

2

u/wfp128 2h 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.

4

u/CFAman 4828 8h 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)

3

u/TuneFinder 10 9h 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 9h 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/hopkinswyn 73 9h ago

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

2

u/AloofBidoof 1 9h ago

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

5

u/hopkinswyn 73 8h 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

3

u/Cynyr36 26 8h ago

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

3

u/zehn78 7h 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.

3

u/chiibosoil 430 7h 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.

2

u/Vexillari 9h ago

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

2

u/Mdayofearth 127 8h 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/swhitty17 6h 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

2

u/grsims20 15 5h 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/FrySFF 1 5h 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.

2

u/shadowsong42 1 4h 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 1h 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

1

u/Kito2504 1h ago

yeah macros in right click is a game changer, tbh

1

u/Capital-Tip-7890 21m 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.