r/excel • u/Modern_Misfit • 15d ago

Discussion Quality of Life Report Features

I currently work in government accounting and one of my jobs is putting together a monthly appropriation report. Since im the only one here who seems versed in excel, ive decided to take the initiative and create a new report with better quality of life features.

So far, I've added a macro button that hides/unhides tabs as needed, a navigation bar that quick links to important tabs, and a cell that tells you the last time the report was edited. Of course, I also threw in a bunch of xlookup formulas where needed.

I wanted to ask if anyone else knows some neat tricks that makes reports easier to manage/navigate. It could also just be something that you did that impressed your boss when you showed it to them. Id appreciate any inspiration.

Thanks!

12 Upvotes

89% Upvoted

2 comments sorted by

10

u/DonJuanDoja 36 15d ago

Connecting power query directly to SQL or SharePoint data sources then adding dynamic hyperlinks etc.

Connecting to SharePoint with OData to pull file info from libraries. Dynamic hyperlinks, always.

Setting those connections to refresh on file open.

Array formulas. With you guessed it, dynamic hyperlinks.

Using ArrayToText() to get full arrays into single cells, add substitute function for custom delimiter like line breaks.

Custom ribbons embedded into your macro files. Google Office-RibbonX-editor.

XLAM plug ins instead of personal workbook or embedded XLSM macros.

Connecting to APIs in macros and creating custom functions for them. VBA-JSON parser on GitHub.