Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Excel: am I hopelessly old-fashioned?

Discussions regarding financial software
MDW1954
Lemon Quarter
Posts: 2358
Joined: November 4th, 2016, 8:46 pm
Has thanked: 526 times
Been thanked: 1011 times

Excel: am I hopelessly old-fashioned?

#446696

Postby MDW1954 » September 30th, 2021, 5:58 pm

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

tjh290633
Lemon Half
Posts: 8209
Joined: November 4th, 2016, 11:20 am
Has thanked: 913 times
Been thanked: 4097 times

Re: Excel: am I hopelessly old-fashioned?

#446710

Postby tjh290633 » September 30th, 2021, 6:46 pm

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

Alaric
Lemon Half
Posts: 6035
Joined: November 5th, 2016, 9:05 am
Has thanked: 20 times
Been thanked: 1400 times

Re: Excel: am I hopelessly old-fashioned?

#446732

Postby Alaric » September 30th, 2021, 8:08 pm

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.

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Excel: am I hopelessly old-fashioned?

#446745

Postby Itsallaguess » September 30th, 2021, 8:34 pm

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! :D

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..) -

Code: Select all

Sub Highlight_Row_Groups()

Dim intRow As Integer: intRow = 2 ' start at 2, cause there's nothing to compare the first row with

' ***************************
' This section defines the column of data with the change defining the row-group colour change (Column 2 by default)
    Dim intCol As Integer: intCol = 2 ' define the column with changing values
' ***************************
    Dim Colr1 As Boolean: Colr1 = True ' Will flip True/False; adding 2 gives 1 or 2
    Dim lngColors(2 + True To 2 + False) As Long   ' Indexes : 1 and 2
          ' True = -1, array index 1.    False = 0, array index 2.
    lngColors(2 + False) = RGB(235, 235, 235) ' lngColors(2) = light grey
    lngColors(2 + True) = RGB(255, 255, 255) '  lngColors(1) = white

    Do While Cells(intRow, 1) <> ""
        'check for different value in intCol, flip the boolean if it's different
        If (Cells(intRow, intCol) <> Cells(intRow - 1, intCol)) Then Colr1 = Not Colr1
        Rows(intRow).Interior.Color = lngColors(2 + Colr1) ' one colour or the other
        ' Optional : retain borders (these no longer show through when interior colour is changed) by specifically setting them
        With Rows(intRow).Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = RGB(220, 220, 220)
        End With
        intRow = intRow + 1
    Loop

End Sub


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

Howard
Lemon Quarter
Posts: 2178
Joined: November 4th, 2016, 8:26 pm
Has thanked: 885 times
Been thanked: 1017 times

Re: Excel: am I hopelessly old-fashioned?

#446750

Postby Howard » September 30th, 2021, 8:46 pm

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

genou
Lemon Quarter
Posts: 1070
Joined: November 4th, 2016, 1:12 pm
Has thanked: 177 times
Been thanked: 370 times

Re: Excel: am I hopelessly old-fashioned?

#446755

Postby genou » September 30th, 2021, 9:18 pm

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.

AleisterCrowley
Lemon Half
Posts: 6381
Joined: November 4th, 2016, 11:35 am
Has thanked: 1880 times
Been thanked: 2026 times

Re: Excel: am I hopelessly old-fashioned?

#446757

Postby AleisterCrowley » September 30th, 2021, 9:35 pm

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

Lanark
Lemon Quarter
Posts: 1321
Joined: March 27th, 2017, 11:41 am
Has thanked: 595 times
Been thanked: 582 times

Re: Excel: am I hopelessly old-fashioned?

#446758

Postby Lanark » September 30th, 2021, 9:36 pm

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

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

monabri
Lemon Half
Posts: 8396
Joined: January 7th, 2017, 9:56 am
Has thanked: 1539 times
Been thanked: 3428 times

Re: Excel: am I hopelessly old-fashioned?

#446759

Postby monabri » September 30th, 2021, 9:42 pm

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 :roll: )

MDW1954
Lemon Quarter
Posts: 2358
Joined: November 4th, 2016, 8:46 pm
Has thanked: 526 times
Been thanked: 1011 times

Re: Excel: am I hopelessly old-fashioned?

#446776

Postby MDW1954 » September 30th, 2021, 10:34 pm

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

AleisterCrowley
Lemon Half
Posts: 6381
Joined: November 4th, 2016, 11:35 am
Has thanked: 1880 times
Been thanked: 2026 times

Re: Excel: am I hopelessly old-fashioned?

#446785

Postby AleisterCrowley » September 30th, 2021, 11:03 pm

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 :roll: )


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

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

MDW1954
Lemon Quarter
Posts: 2358
Joined: November 4th, 2016, 8:46 pm
Has thanked: 526 times
Been thanked: 1011 times

Re: Excel: am I hopelessly old-fashioned?

#446792

Postby MDW1954 » September 30th, 2021, 11:19 pm

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

staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Re: Excel: am I hopelessly old-fashioned?

#446835

Postby staffordian » October 1st, 2021, 7:39 am

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.

MDW1954
Lemon Quarter
Posts: 2358
Joined: November 4th, 2016, 8:46 pm
Has thanked: 526 times
Been thanked: 1011 times

Re: Excel: am I hopelessly old-fashioned?

#446875

Postby MDW1954 » October 1st, 2021, 9:45 am

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

Gerry557
Lemon Quarter
Posts: 2003
Joined: September 2nd, 2019, 10:23 am
Has thanked: 172 times
Been thanked: 542 times

Re: Excel: am I hopelessly old-fashioned?

#447269

Postby Gerry557 » October 2nd, 2021, 4:10 pm

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!

MDW1954
Lemon Quarter
Posts: 2358
Joined: November 4th, 2016, 8:46 pm
Has thanked: 526 times
Been thanked: 1011 times

Re: Excel: am I hopelessly old-fashioned?

#447299

Postby MDW1954 » October 2nd, 2021, 5:40 pm

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

TonyB
Posts: 44
Joined: November 8th, 2016, 10:38 am
Has thanked: 1 time
Been thanked: 9 times

Re: Excel: am I hopelessly old-fashioned?

#447423

Postby TonyB » October 3rd, 2021, 10:49 am

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.

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Excel: am I hopelessly old-fashioned?

#447504

Postby Itsallaguess » October 3rd, 2021, 3:03 pm

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

scotia
Lemon Quarter
Posts: 3561
Joined: November 4th, 2016, 8:43 pm
Has thanked: 2371 times
Been thanked: 1943 times

Re: Excel: am I hopelessly old-fashioned?

#448111

Postby scotia » October 5th, 2021, 4:51 pm

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.

JohnnyCyclops
Lemon Slice
Posts: 301
Joined: November 15th, 2016, 9:19 pm
Has thanked: 201 times
Been thanked: 124 times

Re: Excel: am I hopelessly old-fashioned?

#448764

Postby JohnnyCyclops » October 8th, 2021, 2:30 pm

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.


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 11 guests