The Lemon Fool

My spreadsheet usage dates back to the Visicalc era, before Lotus 123.

When I was employed by two of the Big Six consulting firms in the 80s and very early 90s, Lotus 123 ruled, although Excel was on the horizon. I remained a Lotus 123 user for many years, once I'd left the corporate world, and probably didn't start moving in earnest to Excel until I had to, because clients were sending me Excel spreadsheets, or occasionally wanting analyses done using Excel spreadsheets. In 2009 I took a fairly serious Excel course, and have since taken quite a few more. I'm comfortable with macros, and as some here know, my VBA skills (while limited), have enabled me to write and develop some user-defined functions to undertake logarithmic linear least squares regression. (A shout-out here to user IAAG, who helped me over one or two stumbling blocks with that.)

All of which is intended to persuade you all that I'm not a complete Excel numpty. Although you may yet change your mind...

So: what does 'best-practice' or at least common-practice Excel actually look like these days?

Because I'm increasingly seeing spreadsheets on the Internet that are in table format -- you know: sortable columns, green-and-white bands etc. Is that now the standard? Is that what happens in the corporate world?

I'm asking partly because I've been importing the data from IAAG's regular IT posts, and that's the mode it gets transferred over in. I routinely remove all that formatting, but should I be leaving it in, and moving with the times? Certainly, the green-and-white lines aid readability, and reading IAAG's header data into the column headings isn't difficult. I could certainly get used to it.

So, in essence, what I'm asking is: what does good-practice corporate Excel look like these days?

MDW1954

MDW1954 wrote:So, in essence, what I'm asking is: what does good-practice corporate Excel look like these days?

MDW1954

I have no idea, Malcolm. My IT experience goes back to the 1980s, starting with a BBC Model B and Ultracalc, then into more serious stuff with Lotus and Prime Infobasic, and then on to Excel in various forms, but I've been away from it for 20 years now. Our parent company in the USA had a variety of Excel sheets, for various functions associated with furnace performance and pollution, and once upon a time I could do macros, but I have long since forgotten. My attitude would be to use them as you get them, assuming that the colours have significance. I have some conditional formatting in my own spreadsheets, but very simple.

TJH

MDW1954 wrote:Because I'm increasingly seeing spreadsheets on the Internet that are in table format -

My knowledge is also twenty years out of date, but I do recall that if you structured sheets such that row 1 was always column headings and rows 2 to n were always data, then database functions could be used. That also included being able to run sql queries.

MDW1954 wrote:
I'm asking partly because I've been importing the data from IAAG's regular IT posts, and that's the mode it gets transferred over in.

I routinely remove all that formatting, but should I be leaving it in, and moving with the times?

Certainly, the green-and-white lines aid readability, and reading IAAG's header data into the column headings isn't difficult. I could certainly get used to it.

So, in essence, what I'm asking is: what does good-practice corporate Excel look like these days?

Dunno - I just kick it until it does what I want it to, and an Excel expert in my mind is just someone who knows a few extra places to kick!

Macros are brilliant for this sort of stuff though Malcolm, so have a go at this to see how quick and powerful they can be with these sorts of financial-data lists we often see around here -

1. Open a new Excel workbook

2. Generate some data so that Sheet1 looks something similar to this - https://i.imgur.com/zxUyNuF.png

3, Note that in the above data-set, we've got groups of 'Sectors' listed in Column 2 - this is a main driver of what we're going to do here, so keep this in mind...

4. Press ALT+F11 to open up the Visual Basic Editor window

5. In the left-hand panel, right-click the VBA Project for the new workbook, which will often be called something like Book1

6. In the right-click context-menu, select 'Insert', and then select 'Module'

7. In the left-hand panel, double-click the new module

8. In the centre VBA window, copy and paste the following VBA code (note there's a 'Select All' link at the top of the following code window to help with the copy and paste..) -



9. Go back to your Excel worksheet, and open the 'Developer' tab (instructions here if it's not currently visible - https://support.microsoft.com/en-us/topic/show-the-developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45)

10. On the 'Developer' tab, select the 'Macros' button

11. In the macro window that appears, you should see a macro called 'Highlight_Row_Groups' - select it and then click the 'Run' button

12. Once you've run the macro, your worksheet should now look like this, with the 'Sector' data in Column 2 defining the change-points between the highlighted row-groups - https://i.imgur.com/4LnIilT.png

Note that the VBA code in the above window is currently set to look at Column 2 for where the groups of change-related data is that we want to highlight, which in this example is the 'Sector xxx' data, but a different data-set might want to change that column number to something else, so I've put the column-variable to change within some asterisk lines at the top of the code, which will hopefully make it a simple change if you were to want to define a different column...

Anyhow - I hope the above is useful - I'm sure it's not 'best-practice' in any real sense of the word, but best-practice for me is to learn how to use a tool so it can be as flexible in your hands as you need it to be, and this sort of stuff is tremendously useful once you set off down the rabbit-hole...

Cheers,

Itsallaguess

MDW1954 wrote:When I was employed by two of the Big Six consulting firms in the 80s and very early 90s, Lotus 123 ruled, although Excel was on the horizon.

So, in essence, what I'm asking is: what does good-practice corporate Excel look like these days?

MDW1954

Your reminiscence about Lotus 123 reminded me of the past. Hopefully not too far off topic. And it concludes with a possible answer.

I was employed by a multi-national company and in my IT team was a bright young guy. He told me Lotus 123 had a number of flaws and he could improve it and “bolt” it onto the front of our main frame programme to improve our report formats. I remember giving him avuncular advice that his talents were wasted in our large company. Apparently others remembered the conversation with amusement. I went on to concentrate (as far as I was concerned) on the big issues at hand. He drifted off to the USA.

Around ten years later, one of my team, who’d left and gone to America sent me an email. With some delight he recounted that he’d sat next to the “young man” travelling first class on an internal flight. He sent me his regards, thanked me for the advice which he’d followed all those years ago. He thought I’d like to know that he’d just sold his IT company for not far off a billion dollars. He had a modest 70% stake.

I’ve followed his progress since. He’s made some further very shrewd major investments in other multi bagging IT companies.

I comfort myself that my concentrating on the big corporate issues led to a reasonable pension.

As far as I know, he now uses Excel to monitor his wealth ……… or maybe an even better personalised program he's designed himself. I'm sure it's multicoloured

regards

Howard

MDW1954 wrote:So, in essence, what I'm asking is: what does good-practice corporate Excel look like these days?

MDW1954

I seriously doubt there is such a thing, unless your question is purely about presentation, on which I have no current knowledge. I left ~10 years ago, and even then our (IT) refrain was that no serious work should be done in f*cking spreadsheets, which defy any effort to ensure that their results are anything more than the imagination of the person(s) who wrote and edited them. As a front end to a proper auditable database, fine. But as a tool of first resort no. We did not get a lot of traction. Because they grow like weeds, and people think they understand them.

We (very large telecoms co) seem to entirely run on Excel - even for things that would be better on a proper database
Excel for work -including minutes of meetings sometimes!- and PowerPoint (spit!) for presenting to the uninterested

This talk is great for quickly covering the major improvements added in the last few years

https://www.youtube.com/watch?v=0nbkaYsR94c

Maybe Green and white bands are the Google spreadsheet defaults - perhaps people like the default or are not competent enough to change the colour (color) scheme?

I don't think that there is a set standard - wouldn't companies wish to make their mark on their presentation ?

(we had a CEO who would not listen to a powerpoint presentation if one dared to use Times New Roman as opposed to Arial font )

Thanks all. I thought it might help if I actually posted an image of what I'm talking about.

Here's how IAAG's table appears, once I've downloaded it into Excel. (A macro deletes all the "------------" lines, btw.) See how the headers are sortable etc.

https://i.imgur.com/5q7TiFD.png

My point is that I'm seeing this sort of view as becoming standard, and wondering if I'm missing out.

More detailed reply to follow over the weekend. And especial thanks to IAAG: that macro looks very handy.

MDW1954

monabri wrote:Maybe Green and white bands are the Google spreadsheet defaults - perhaps people like the default or are not competent enough to change the colour (color) scheme?

I don't think that there is a set standard - wouldn't companies wish to make their mark on their presentation ?

(we had a CEO who would not listen to a powerpoint presentation if one dared to use Times New Roman as opposed to Arial font )

Vague memories the old teletype/computer dot matrix printer paper was white and green (sometimes)

[edit] Aha... https://en.wikipedia.org/wiki/Continuous_stationery

AleisterCrowley wrote:Vague memories the old teletype/computer dot matrix printer paper was white and green (sometimes)

Yes, you're right -- and that connection had escaped me, despite using tonnes of the stuff when doing my PhD.

That could well be where green and white comes from.

But the sortable column headers...? Is that now standard good practice?

MDW1954

MDW1954 wrote:
But the sortable column headers...? Is that now standard good practice?

MDW1954

That seems to be a result of inserting a table rather than leaving the data as it was originally input, though this feature can then be switched off if not needed.

I'd not have known that had I not watched the YouTube link supplied by Lanark a few posts above.

This action of converting the data to a table also produces the fancy colours.

staffordian wrote:

MDW1954 wrote:
But the sortable column headers...? Is that now standard good practice?

MDW1954

That seems to be a result of inserting a table rather than leaving the data as it was originally input, though this feature can then be switched off if not needed.

I'd not have known that had I not watched the YouTube link supplied by Lanark a few posts above.

This action of converting the data to a table also produces the fancy colours.

Yes, that's exactly the process involved -- it's exactly what I do with IAAG's table, and the result is what you see. Excel's table functions are quite powerful, and there is even a separate analytics language for them, ie, not VBA.

There are some very good (free) edX courses showing how to do it. I did them several years ago, and they're very handy.

MDW1954

I find that most people don't know much about It so leave it to someone who is deemed a wizz.

This is generally someone who has used excel a couple of times, so is the one eyed man in the kingdom of the blind.

Most are little more than pretty tables with no automation.

In most cases a database would be a much better option but the wizzis don't know how to do that. I have met some really good excel designers but they are far and few Inbetween.

I do like to be impressed by someone who teaching me something new. I did watch whilst a calculator was dragged out to get the result off two excel spreadsheets...... If only they was a way to merge them into different sheets in one spreadsheet and have a place to get the final result. I bit my lip!

Despite being a moderator, I don't have moderation rights to this board, so I can't edit user Itsallaguess's excellent macro post above.

If anyone is thinking of doing what IAAG suggests (and I recommend it), then the image below may help Fools to avoid tripping up (like I stupidly did) when changing column 2 to whatever the column number needs to be.

This is the section of the code to change:

https://i.imgur.com/WRqEEkg.png

Thanks again to IAAG for posting this macro, and especial thanks for helping me debug its implementation.

MDW1954

Back to the OP. Not sure what good corporate practice is these days as I've not been part of that scene for a while, but separating data from the computed and displayed information was generally a good design principle and tables in Excel have lots of advantages: named ranges, ease of adding records and computed columns etc.

Using Excel's Power Query functionality allows users to access internal/external databases, webpage data and csv files etc wrangle the data and often output as a table for further analysis all with the ability to get the latest data at the push of a button. No more out of date local data copies!

Power Query is a fantastic tool for accessing remote data, relatively easy and well worth learning the basics

if you have an interest and an hour or two spare.

How do we know Excel was written by a bloke?

Because it always tries to turn things into dates when they're not....

Cheers!

Itsallaguess

Gerry557 wrote:
In most cases a database would be a much better option

Yes - if you want to hold and retrieve historical information on your financial actions, you need a database. If you use Microsoft Office, then Access is the obvious choice. You can Import and export Excel spreadsheets as required to/from your Access tables, and the language behind Access is VBA - the same as for Excel.

MDW1954 wrote:
So, in essence, what I'm asking is: what does good-practice corporate Excel look like these days?

MDW1954

Excel is still king (or queen), alongside PowerPoint, in our business. Starting to see PowerBI get more traction for data visualisation. I believe it can sit on top of source Excel data. https://powerbi.microsoft.com/

I think the green/white banding is just table formatting, of which there are many, both in tables and also in Pivot Table formats. I quite like Pivots to analyse and slice data but believe there may be more efficient tools in Excel that do the job but require more fiddling/coding. I've never VBA'd and rarely macro'd. MS Access (database) left me cold back in Office 2000 and I never engaged with it again.