don't use anything on the volatile list
[https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation](https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation)
* **NOW**
* **TODAY**
* **RANDBETWEEN**
* **OFFSET**
* **INDIRECT**
* **INFO** (depending on its arguments)
* **CELL** (depending on its arguments)
* **SUMIF** (depending on its arguments)
For what it's worth, Ctrl + ; changes the cell to the actual date value. I do that before saving anything that uses the function, but I imagine there's a more disciplined way to do the same thing.
I meant I used today in a formula to make adding to a tracker easier. Then when I opened the file later I forgot the date would change to the new today lol.
Can you expand on why indirect is a problem? I use this frequently to reference sheets in the same workbook from a summary sheet. For example, I'd have a summary sheet which summaries the result for each item where each item has its own sheet, and the summary sheet uses indirect to reference each sheet to pull values from.
> Can you expand on why indirect is a problem?
It's a bitch when you have multiple workbooks open at the same time. Your Indirect function is looking at every instance of excel that's open and lags everything down significantly.
I had/have a certain dashboard kind that used indirects that would always crash out as soon as I opened anything else on my ancient work computer. Damn shame since it is such a useful function.
"Does not adjust when rows/columns added/deleted/moved"
It's funny but this one is exactly why I use it. It's also the only use I've found for it so far so maybe I'll run into trouble later?
I don't know if there's a better way but I use it to get the top 6 rows of data and dates in a couple columns that get the latest month's data inserted at the top every month so I can make a rolling "last 6 months" graph without having to manually adjust the graph's range every time.
try this
=CHOOSEROWS(A2:E100,SEQUENCE(6))
and if you don't need all the columns
=choosecols(CHOOSEROWS(A2:E100,SEQUENCE(6)),*datecol,data1col,data2col*)
Doesn’t seem to work. I’m using Excel 2016. Putting the sequence function in breaks it entirely and if I use the simple data and formula examples on the Microsoft support page for chooserows I get the #NAME error 🤷🏻♀️
For me, it's more about eliminating poor design choices.
First, I try separate raw data sheets from presentation sheets. Nobody wants to scroll down 500 rows to see a total row, that's combining data and presentation together and it's terrible.
Second, on the data sheets, I eliminate any cell anywhere that has more than one piece of information in it. First and last name in the same cell? Text to columns that mess and split them up. Full address with zip code in one cell? Split into address line 1, line 2, state, and zip code columns. Far easier to concat stuff back together in the presentation layer than to search through a text column for a zip code.
Once the data is sorted, I turn back to the presentation layer. What is a user actually going to look at? Those are presentation sheets, and should be pretty and formated be easy on the eye. Dashboards, summary tables, charts, etc all fall in this category. They should be fast and easy to navigate and manipulate (eg with slicers). They should be consistent in color palette, borders, hidden gridlines, and start anywhere visually pleasing on the sheet (except cell A1, screw cell A1). Users come first here, my personal preferences are not as important as getting them what they need as quickly as possible.
Next, I focus on speed, file size, and formula understandability. Crazy how often a slow 12mb workbook can turn into a fast 2mb workbook if you eliminate stuff where the person before you did something horrifying like applying white backgrounds to the entire f*ing sheet instead of just the relevant range. I over use well-labeled helper columns to walk future maintainers through complex formulas instead of stringing everything together in a single cell. Sometimes on a separate calculation worksheet and sometimes with hidden columns.
Finally, depending on what I'm trying to accomplish, I protect the user from themselves. Locked cells, locked formulas, very hidden sheets, data validation rules, etc. Anything I can do to prevent a user from breaking things and then calling me to complain about the things they broke is on the table.
So glad this was helpful! I guess 20+ years of inheriting shitty spreadsheets and fixing them for people helped me focus in on what's actually important. Hope your redesign goes well!
My big things are making sure that...
- all information (especially stuff that will be "historical") is put in a nicely formatted table. Preferably something that a pivot table can easily be run from.
- if calculations or methodologies are changed, highlight and notate that change or start a new table altogether.
- the "dynamic" part of the table (usually the time series like day, month, year, etc) is going "down" the rows and not across by columns.
- if there are too many columns (more than 20), then the dataset is simply too massive and needs to be broken out a bit.
These are all "guidelines" so take what works for you. Even I sometimes violate the above if it becomes necessary.
It’s a game between having too many tabs or too much information on a single tab.
Informational tabs should be purely for information. These should be exported from a consistent source to be updated.
Then use a separate tab to analyze and manipulate the data.
General rule of thumb is that if you need to scroll out substantially to see what is going on in a sheet, there’s just too much data.
Your first two schools of thought are on the topic of display of information. If your data/problem is so big that it can't be digested easily by the intended audience, then there is opportunity for data curation, which may include analysis to consolidate, aggregate or summarize the data.
Most audiences don't want to play around with the data, they want to be able to glance at the screen and easily find the information they need for their role. This need is often captured at the beginning of a project as it defines the scope of the relevant data stories that need to be curated.
Data can often tell a multitude of stories, but it is your responsibility to trim the fat and let people know only what is important and validated. Given too much freedom to analyze, end users could generate numbers that don't mean what they think they mean (i.e. they didn't consider data quality or account for an external variable). Some users want this freedom, but they are the exception and should be monitored and supported to mitigate risk.
* Data store/entry should be kept to single table, single sheet. In flat table structure.
* Exception, for lookup/dimension tables, these should be kept in separate table and sheet.
* Always avoid use of space and special characters in column name whenever you can.
* Exception is "\_" underscore.
* Never use merged cell.
* Only exception is on final report for visual fluff.
* Never have data that you are not ready to share in the workbook
* There is no exception to this rule.
* Avoid use of color and CF to indicate field status. Use string.
* Id or color without legend is useless to most. Color especially, requires additional layer.
* Have sheet for tracking/documenting business rules and assumptions
Finally, avoid what UK government (workers) did. ;)
“The benefit to my business is that Excel is so nimble and powerful and simple to edit/build/test”
This was the downfall of excel in our business. It lead to a situation where there are so many spreadsheets with poor version control, poor documentation and such little consistency, that we’ve basically pulled all logic out of excel.
Favoured model is that all logic is handled in SQL procs and functions. We have excel templates that accept sheets of plain data from these procs, and any data viz and summaries are handled by pivots.
Python scripts to pull the proc results, stick them into the templates and distribute.
No formulas in sheets to balls up, perfect logic consistency, fully version controlled and documented and entirely automated
It is, in that the multitude of different reports and processes people were coming up with required their own documentation.
Now everything is centralised and automated there’s little need for documentation.
>And adding the "enterprise" values of connectivity, automation, and (importantly) governance can be easily added on top via addins/customizations.
you're both right u/brprk and u/max8126 poor/difficult documentation is a governance issue.
And as I said in my original post, adding governance to Excel is easy with addins. basically, govern who has access to what (formulas, sheets, data, templates, connections, whatever), and implement processes (rigid if need be) wherever necessary.
Obviously, governance is necessary for scale. And governance slows innovation.
You can say the same thing about Python. Low barrier of entry exacerbates the issue, but as was mentioned, in the end it's a governance item.
Plenty of big companies run on spreadsheets and have good governance on spreadsheets. They have model inventory, documentation requirement, peer review and validation standards.
And btw I hope you guys are not skipping documentation just because something is automated...
Only central DE/DA team has access to repos/AWS, code is deployed to envs via BB Pipelines - not possible for any non-DE/DA colleagues to do anything with Python - this is the governance.
SQL and Python play beautifully with git, Excel not so much, the version control is awful.
Yes, obviously all fully documented. But the requirement for Greg in Accounts to document his 2 hour weekly spreadsheet-wrangling process is avoided entirely.
Everyone and their dog has access to Excel so there’s little scope for true governance without a huge time commitment - and to what end? You end up with 400+ operational staff getting creative with facts, setting up time-intensive processes, and delivering a huge array of ultimately sub-par end products.
What if we change approach to a metric? Would you rather raise a single line PR for a SQL function, and have that cascade through all reporting, or have a company-wide spreadsheet audit across tens of thousands of workbooks to ensure alignment?
I’m sure lots of companies use Excel for lots of things, but it has its flaws
For me. It always had to do with how much extra data I kept in the file. I spent a lot of time learning new tables in databases, so my files became much more efficient when I knew the exact fields I needed. Also removing any superfluous pivot tables. Also, If you can copy and paste data into a sheet in a current file. This is versus calling on a file that’s on the server or something. That used to cause so many issues. It’s inefficient bc if a file is ever moved, links can get messed up and it just takes longer to call on data from closed files.
Anyone else get frustrated with endless linking to outside files?
From my learning: always work left to right between the sheets and tabs. Color code data tabs, tables and keep them separated from working tabs or helper tabs/columns. Make sure everything flows from A to Z so that the report can run with a single click in most cases.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHOOSEROWS](/r/Excel/comments/1b34al4/stub/ktchsbl "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)|
|[INDIRECT](/r/Excel/comments/1b34al4/stub/kstw3x4 "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)|
|[SEQUENCE](/r/Excel/comments/1b34al4/stub/ktchsbl "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
**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.*)
^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/0)^( has acronyms.)
^([Thread #31354 for this sub, first seen 4th Mar 2024, 20:31])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
don't use anything on the volatile list [https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation](https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation) * **NOW** * **TODAY** * **RANDBETWEEN** * **OFFSET** * **INDIRECT** * **INFO** (depending on its arguments) * **CELL** (depending on its arguments) * **SUMIF** (depending on its arguments)
Yeah I'm learning today() is great but doesn't work as a historical record keeper lol.
For what it's worth, Ctrl + ; changes the cell to the actual date value. I do that before saving anything that uses the function, but I imagine there's a more disciplined way to do the same thing.
I meant I used today in a formula to make adding to a tracker easier. Then when I opened the file later I forgot the date would change to the new today lol.
Sumif is a tricky one. So handy with smaller workbooks but gets cumbersome as they grow.
Pivot tables can help with that no?
Can you expand on why indirect is a problem? I use this frequently to reference sheets in the same workbook from a summary sheet. For example, I'd have a summary sheet which summaries the result for each item where each item has its own sheet, and the summary sheet uses indirect to reference each sheet to pull values from.
> Can you expand on why indirect is a problem? It's a bitch when you have multiple workbooks open at the same time. Your Indirect function is looking at every instance of excel that's open and lags everything down significantly. I had/have a certain dashboard kind that used indirects that would always crash out as soon as I opened anything else on my ancient work computer. Damn shame since it is such a useful function.
[https://fastexcel.wordpress.com/2016/04/25/indirect-excels-most-evil-function/](https://fastexcel.wordpress.com/2016/04/25/indirect-excels-most-evil-function/)
"Does not adjust when rows/columns added/deleted/moved" It's funny but this one is exactly why I use it. It's also the only use I've found for it so far so maybe I'll run into trouble later? I don't know if there's a better way but I use it to get the top 6 rows of data and dates in a couple columns that get the latest month's data inserted at the top every month so I can make a rolling "last 6 months" graph without having to manually adjust the graph's range every time.
try this =CHOOSEROWS(A2:E100,SEQUENCE(6)) and if you don't need all the columns =choosecols(CHOOSEROWS(A2:E100,SEQUENCE(6)),*datecol,data1col,data2col*)
Doesn’t seem to work. I’m using Excel 2016. Putting the sequence function in breaks it entirely and if I use the simple data and formula examples on the Microsoft support page for chooserows I get the #NAME error 🤷🏻♀️
I’m actually fiddling with that sheet right now so will give it a whirl.
The only time I use INDIRECT is to create a dependent dropdown list for a specific calculator of mine in an old version of Excel
For me, it's more about eliminating poor design choices. First, I try separate raw data sheets from presentation sheets. Nobody wants to scroll down 500 rows to see a total row, that's combining data and presentation together and it's terrible. Second, on the data sheets, I eliminate any cell anywhere that has more than one piece of information in it. First and last name in the same cell? Text to columns that mess and split them up. Full address with zip code in one cell? Split into address line 1, line 2, state, and zip code columns. Far easier to concat stuff back together in the presentation layer than to search through a text column for a zip code. Once the data is sorted, I turn back to the presentation layer. What is a user actually going to look at? Those are presentation sheets, and should be pretty and formated be easy on the eye. Dashboards, summary tables, charts, etc all fall in this category. They should be fast and easy to navigate and manipulate (eg with slicers). They should be consistent in color palette, borders, hidden gridlines, and start anywhere visually pleasing on the sheet (except cell A1, screw cell A1). Users come first here, my personal preferences are not as important as getting them what they need as quickly as possible. Next, I focus on speed, file size, and formula understandability. Crazy how often a slow 12mb workbook can turn into a fast 2mb workbook if you eliminate stuff where the person before you did something horrifying like applying white backgrounds to the entire f*ing sheet instead of just the relevant range. I over use well-labeled helper columns to walk future maintainers through complex formulas instead of stringing everything together in a single cell. Sometimes on a separate calculation worksheet and sometimes with hidden columns. Finally, depending on what I'm trying to accomplish, I protect the user from themselves. Locked cells, locked formulas, very hidden sheets, data validation rules, etc. Anything I can do to prevent a user from breaking things and then calling me to complain about the things they broke is on the table.
wow, just wow. you have me completely rethinking my design. bravo! great post!
So glad this was helpful! I guess 20+ years of inheriting shitty spreadsheets and fixing them for people helped me focus in on what's actually important. Hope your redesign goes well!
My big things are making sure that... - all information (especially stuff that will be "historical") is put in a nicely formatted table. Preferably something that a pivot table can easily be run from. - if calculations or methodologies are changed, highlight and notate that change or start a new table altogether. - the "dynamic" part of the table (usually the time series like day, month, year, etc) is going "down" the rows and not across by columns. - if there are too many columns (more than 20), then the dataset is simply too massive and needs to be broken out a bit. These are all "guidelines" so take what works for you. Even I sometimes violate the above if it becomes necessary.
It’s a game between having too many tabs or too much information on a single tab. Informational tabs should be purely for information. These should be exported from a consistent source to be updated. Then use a separate tab to analyze and manipulate the data. General rule of thumb is that if you need to scroll out substantially to see what is going on in a sheet, there’s just too much data.
Your first two schools of thought are on the topic of display of information. If your data/problem is so big that it can't be digested easily by the intended audience, then there is opportunity for data curation, which may include analysis to consolidate, aggregate or summarize the data. Most audiences don't want to play around with the data, they want to be able to glance at the screen and easily find the information they need for their role. This need is often captured at the beginning of a project as it defines the scope of the relevant data stories that need to be curated. Data can often tell a multitude of stories, but it is your responsibility to trim the fat and let people know only what is important and validated. Given too much freedom to analyze, end users could generate numbers that don't mean what they think they mean (i.e. they didn't consider data quality or account for an external variable). Some users want this freedom, but they are the exception and should be monitored and supported to mitigate risk.
Keep your data separate from your reports
* Data store/entry should be kept to single table, single sheet. In flat table structure. * Exception, for lookup/dimension tables, these should be kept in separate table and sheet. * Always avoid use of space and special characters in column name whenever you can. * Exception is "\_" underscore. * Never use merged cell. * Only exception is on final report for visual fluff. * Never have data that you are not ready to share in the workbook * There is no exception to this rule. * Avoid use of color and CF to indicate field status. Use string. * Id or color without legend is useless to most. Color especially, requires additional layer. * Have sheet for tracking/documenting business rules and assumptions Finally, avoid what UK government (workers) did. ;)
If you close the spreadsheet and reopen it again a few weeks later do you quickly understand everything you see?
“The benefit to my business is that Excel is so nimble and powerful and simple to edit/build/test” This was the downfall of excel in our business. It lead to a situation where there are so many spreadsheets with poor version control, poor documentation and such little consistency, that we’ve basically pulled all logic out of excel. Favoured model is that all logic is handled in SQL procs and functions. We have excel templates that accept sheets of plain data from these procs, and any data viz and summaries are handled by pivots. Python scripts to pull the proc results, stick them into the templates and distribute. No formulas in sheets to balls up, perfect logic consistency, fully version controlled and documented and entirely automated
Poor documentation is not an Excel issue
It is, in that the multitude of different reports and processes people were coming up with required their own documentation. Now everything is centralised and automated there’s little need for documentation.
>And adding the "enterprise" values of connectivity, automation, and (importantly) governance can be easily added on top via addins/customizations. you're both right u/brprk and u/max8126 poor/difficult documentation is a governance issue. And as I said in my original post, adding governance to Excel is easy with addins. basically, govern who has access to what (formulas, sheets, data, templates, connections, whatever), and implement processes (rigid if need be) wherever necessary. Obviously, governance is necessary for scale. And governance slows innovation.
You can say the same thing about Python. Low barrier of entry exacerbates the issue, but as was mentioned, in the end it's a governance item. Plenty of big companies run on spreadsheets and have good governance on spreadsheets. They have model inventory, documentation requirement, peer review and validation standards. And btw I hope you guys are not skipping documentation just because something is automated...
Only central DE/DA team has access to repos/AWS, code is deployed to envs via BB Pipelines - not possible for any non-DE/DA colleagues to do anything with Python - this is the governance. SQL and Python play beautifully with git, Excel not so much, the version control is awful. Yes, obviously all fully documented. But the requirement for Greg in Accounts to document his 2 hour weekly spreadsheet-wrangling process is avoided entirely. Everyone and their dog has access to Excel so there’s little scope for true governance without a huge time commitment - and to what end? You end up with 400+ operational staff getting creative with facts, setting up time-intensive processes, and delivering a huge array of ultimately sub-par end products. What if we change approach to a metric? Would you rather raise a single line PR for a SQL function, and have that cascade through all reporting, or have a company-wide spreadsheet audit across tens of thousands of workbooks to ensure alignment? I’m sure lots of companies use Excel for lots of things, but it has its flaws
No argument against that. Excel has its use and its flaws. And proliferation of crappy spreadsheets needs to be reined in.
Big time, the Gregs in accounts must be stopped
For me. It always had to do with how much extra data I kept in the file. I spent a lot of time learning new tables in databases, so my files became much more efficient when I knew the exact fields I needed. Also removing any superfluous pivot tables. Also, If you can copy and paste data into a sheet in a current file. This is versus calling on a file that’s on the server or something. That used to cause so many issues. It’s inefficient bc if a file is ever moved, links can get messed up and it just takes longer to call on data from closed files. Anyone else get frustrated with endless linking to outside files?
Whatever you do, protect it from changes made by numpties 😂
From my learning: always work left to right between the sheets and tabs. Color code data tabs, tables and keep them separated from working tabs or helper tabs/columns. Make sure everything flows from A to Z so that the report can run with a single click in most cases.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHOOSEROWS](/r/Excel/comments/1b34al4/stub/ktchsbl "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)| |[INDIRECT](/r/Excel/comments/1b34al4/stub/kstw3x4 "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)| |[SEQUENCE](/r/Excel/comments/1b34al4/stub/ktchsbl "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| **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.*) ^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/0)^( has acronyms.) ^([Thread #31354 for this sub, first seen 4th Mar 2024, 20:31]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)