The Future of Excel: 8 Bold Predictions!

When developing a financial model, page standards will often communicate the importance of developing a style guide to enhance the presentation and readability of a financial model for those who are external to its initial development. The use of a distinct style should help to enhance the presentation, side effects simplify ongoing model audit, generic and add a level of consistency and professionalism to the overall build of the model.
Standardising styles will always be a difficult, as each model developer will have his/her own preferences with regards to the various style components. However, to engage in best practice, the most important thing is to be consistent within your model stakeholders, that is ensuring consistency in approach between those who are commonly affected, or can be affected by your financial modelling.
The SMART Financial Modelling Methodology, is one of the most widely recognised best practice financial modelling guides, and details a set of principles of presentation that include the following key elements:

  • Visually identify inputs
  • State dimensions and labels concisely
  • Develop customised style guides
  • Worksheets labelled, grouped and colour-coded
  • Include integrity checks
  • Use conditional formatting
  • Use data grouping and a clear heading hierarchy
  • Make good use of the screen with freeze panes
  • Use line summaries
  • Summarise output using plots

The role of presentation within the financial model building process should not be underestimated, as the most complex and well-designed models can lose their impact if they are difficult to interpret, present and audit. As indicated above, an integral part of the principles of presentation is the role of developing a customised style guide.
If the generic cell style set doesn’t suit your taste, you’re left with creating a custom cell style set, and in this article we look at simplifying the process of creating a customised financial modelling cell style set to improve the adoption of a standardised style by model developers. Currently Excel has one built in pre-defined set of ‘Data and Model’ cell styles shown in Figure 1 below:

Figure 1: The Cell Style Sets that are pre-existing within Excel.

It is generally considered good practice, when you are creating a model, to include a style guide whereby someone reviewing your model for the first time can use this sheet to develop an understanding of the flow of the model in terms of its components. To encourage the use of cell styles, the aim is to make the development and use of cell styles an efficient process whereby the application of styles to the financial model is not a burden. With this in mind, the example below, introduces an approach to developing custom financial modelling cell style set, which is easy to set-up and apply as the model is being constructed.

Step 1. – Develop a Style Template

By establishing a style template, this sets a clear guide as to the format of the model for anyone who is required to use it. Cell styles are also workbook specific, i.e. if you open a new workbook the custom cell styles will not be available, so if you create custom cell styles, make sure these are applied to the style template which can be copied across to any workbook to ensure the custom styles you have created will be available.
For the purpose of this exercise, the name you intend to call each cell style should be placed within the cell style you have chosen. Not only will this text function as the name of your cell style, but it also visually displays the text settings you have applied to each style.
In addition, each cell style name MUST be different to the default cell style names or it can’t be created.

Figure 2: Creating your Template

Step 2 – Setting up the VBA Macro

Once the template has been set up, the idea is to have a way to efficiently apply the newly created formats to the custom cell styles menu.  The VBA macro that has been created automates and simplifies this otherwise manual process.
The VBA macro can be copied to your Personal Macro Workbook so that it is available at all times. If your Personal Macro Workbook does not yet exist, the easiest way to create it is to record a macro to it (Figure 3).

Figure 3: Creating a Personal Macro Workbook

Once the personal macro workbook has been created, we can add the macro to it by doing the following:

  • Alt-F11 (Open the Visual Basic Editor)
  • Click the + next to the VBAProject (PERSONAL.XLS)
  • Click the + next to Modules
  • Click on Module1
  • Click on the View Code icon at the top of the Project Explorer

Add the code below:




Sub customStyleAdd()

‘Add custom styles to your workbook by selecting a range of formatted cell styles

‘Also calls customStyleDelete if the user wants to delete all preexisting custom cell styles

Dim cellStyles As Range

Dim rngCell As Range

Dim strMsg As String

Dim answer As Variant

strMsg = “The following cell styles have been created:” & vbCrLf

‘Delete pre-existing custom cell styles

answer = MsgBox(Prompt:=”Do you wish to delete existing custom cell styles?”, _

Buttons:=vbYesNo + vbQuestion, _

Title:=”Delete Existing Cell Styles?”)

If answer = vbYes Then

Call customStyleDelete

End If

On Error Resume Next

‘Select range (or use preselected range) as the source of the preformatted cell styles

Set cellStyles = Application.InputBox _

(Prompt:=”With you mouse, select the custom cell style set you wish to create.”, _

Title:=”Cell Style Range”, _

Default:=Selection.Address, _

Type:=8)

If IsNull(cellStyles.FormulaArray) = False Then

On Error GoTo 0

MsgBox “Please ensure your selection contains:” _

& vbCrLf & vbCrLf & _

“- More than one cell” _

& vbCrLf & vbCrLf & _

“- Includes preformatted cell styles with labels”

Exit Sub

End If

If cellStyles Is Nothing Then

On Error GoTo 0

MsgBox “No range selected.”

Exit Sub

End If

For Each rngCell In cellStyles

If rngCell.Value = “” Then

GoTo nextcell

Else

ActiveWorkbook.Styles.Add Name:=rngCell.Value, BasedOn:=rngCell

strMsg = strMsg + vbCrLf + vbCrLf + Chr(149) + ” ” + rngCell.Value

End If

nextcell:

Next

MsgBox strMsg

End Sub

Sub customStyleDelete()

‘Deletes all preexisting custom cell styles.

Dim xStyle As Style

For Each xStyle In ActiveWorkbook.Styles

If xStyle.BuiltIn = False Then

xStyle.Delete

End If

Next

End Sub

Figure 4: VBA Code that automatically applies the Custom Cell Styles

Step 4 – Running the Macro

Once the template and macro have been initially set up, we can run the macro which will create custom cell styles based on the formatting and names that you have used in your template. This can be done without the macro manually, however it has to be done for each style repeatedly, and the macro also provides the option to delete pre-existing custom cell styles if so desired.

To run the macro:
Click on the ‘Developer’ tab ? ‘Macros’ ? ‘PERSONAL.XLSB!customStyleAdd’ ? ‘Run’.

Figure 5: Running the macro

You will be prompted to select whether you wish to delete the existing custom cell styles, and then more importantly to select the range of cells from which your Custom Cell Style Set will be generated. Cells with no label will be excluded as part of the Custom Set.

Figure 6: Selecting the cell style range.

Once the macro has been successfully run, we can go to Home ? Styles ? Cell Styles to view the newly created custom cell style set.

Figure 7: The custom cell style set.

Step 5 – Adding to the Quick Access Toolbar

The final step involves adding the ‘Cell Styles’ button to the quick access toolbar.  If you click on ‘Cell Styles button’ to display the various styles and then subsequently right click towards the bottom of the open area you have the option to ‘Add to the Quick Access Toolbar’ (see Figure 8). By selecting this option we enable a shortcut icon to be place on the quick access toolbar which is always available to simplify the process of applying the customised cell styles during the model creation process.

Ultimately, the idea is to create a process whereby users can effectively facilitate the set-up of customised cell styles and improve the efficiency related to their application during the model creation process. The use of cell styles is crucial to presentation and model transparency, both of which are key components to widely accepted financial modelling guidelines worldwide.

Figure 8: Adding to the Quick Access Toolbar

Sub customStyleAdd()

‘Add custom styles to your workbook by selecting a range of formatted cell styles

‘Also calls customStyleDelete if the user wants to delete all preexisting custom cell styles

Dim cellStyles As Range

Dim rngCell As Range

Dim strMsg As String

Dim answer As Variant

strMsg = “The following cell styles have been created:” & vbCrLf

‘Delete pre-existing custom cell styles

answer = MsgBox(Prompt:=”Do you wish to delete existing custom cell styles?”, _

Buttons:=vbYesNo + vbQuestion, _

Title:=”Delete Existing Cell Styles?”)

If answer = vbYes Then

Call customStyleDelete

End If

On Error Resume Next

‘Select range (or use preselected range) as the source of the preformatted cell styles

Set cellStyles = Application.InputBox _

(Prompt:=”With you mouse, select the custom cell style set you wish to create.”, _

Title:=”Cell Style Range”, _

Default:=Selection.Address, _

Type:=8)

If IsNull(cellStyles.FormulaArray) = False Then

On Error GoTo 0

MsgBox “Please ensure your selection contains:” _

& vbCrLf & vbCrLf & _

“- More than one cell” _

& vbCrLf & vbCrLf & _

“- Includes preformatted cell styles with labels”

Exit Sub

End If

If cellStyles Is Nothing Then

On Error GoTo 0

MsgBox “No range selected.”

Exit Sub

End If

For Each rngCell In cellStyles

If rngCell.Value = “” Then

GoTo nextcell

Else

ActiveWorkbook.Styles.Add Name:=rngCell.Value, BasedOn:=rngCell

strMsg = strMsg + vbCrLf + vbCrLf + Chr(149) + ” ” + rngCell.Value

End If

nextcell:

Next

MsgBox strMsg

End Sub

Sub customStyleDelete()

‘Deletes all preexisting custom cell styles.

Dim xStyle As Style

For Each xStyle In ActiveWorkbook.Styles

If xStyle.BuiltIn = False Then

xStyle.Delete

End If

Next

End Sub

Spreadsheets are ubiquitous in modern business and Microsoft’s Excel dominates the market. The computer giant has consistently beaten down each rising competitor of the last 10+ years to cement its position as the undisputed king of spreadsheets.

But what of the future of Excel?

In this forward-looking article, symptoms
we make some bold predictions and share some radical ideas about the features and functionality that might appear in Excel over the next few years.

Better Collaboration
Currently Excel is primarily confined to just a single-user. Yes, tadalafil there are beginnings of multi-user collaborative functionality e.g. shared workbooks, order which don’t work very well, and SharePoint Excel. Microsoft have had to play catch up in this area with services like Google Docs, which were much quicker to see the demand for more collaborative, web based business productivity tools.

Prediction #1: Much more functionality in this direction to turn Excel into a true multi-user tool.

Better Security
At the moment security is pretty weak in Excel and can be cracked relatively easily by someone who knows what they’re doing. Because companies store so much confidential and commercially sensitive information in spreadsheets, the demand for better security is high and will only continue to grow.

On the same note, it would be better to be able to define different access privileges (like in a database) so that different people can access certain parts of a file but not others.

Prediction #2: More robust and granular security.

Better Business Intelligence
To be fair, Excel has been getting better in this area, but it has really only scratched the surface so far. Microsoft could take away market share from business intelligence (BI) vendors and ensure continued upgrades for many years to come by enhancing Excel’s BI capabilities to include more of the functionality other BI front-ends already have.

Prediction #3: Microsoft will develop Excel’s BI capabilities to ensure Excel remains a key front-end tool for BI for the masses.

Better Connectivity
It is still too hard for many average users to connect Excel to a database and extract the information they need. I don’t think it really needs to be that hard!

So many people would find this feature really useful but MS Query looks like it hasn’t been touched by a developer for about ten years.

Prediction #4: We will see more development attention in this area to make the connection and extraction process much easier for non-IT people while also allowing IT to setup connections that users can then consume easily.

Device Independence
Given the popularity of iPads and other tablet devices, Microsoft won’t be able to resist these markets, nor should they. They need a product that runs natively on these mobile devices, rather than relying on terminal services apps or apps that only provide 75% compatibility.

Prediction #5: We will see MS Excel for the iPad and Android very soon.

New Visualisations
Excel has some great ways of visualising data in the variety of charts and diagrams included in the software. However, there are still many areas where BI tools and specialist charting packages walk all over Excel.

Apart from Sparklines and some extra styles, very little has changed in charting for about 10 years. If Microsoft make it easy for developers to create and sell their own chart add-ins (see below), this could all change very rapidly.

Prediction #6: We will see this area opened up so that users can get the charts and data visualisations they really want.

MS Access Takeover
Excel’s current tables are a great way for keeping data in a single table – they were the single biggest improvement in Excel 2007 (in my view).

However they fall far short of relational databases when connecting two or more tables together; ensuring records in one table match another table (referential integrity), inputting data into more than one table and reporting from more than one table.

With some relatively minor enhancements, Excel tables could take over much of the functionality that Microsoft Access has been doing with SQL Server left to handle the bigger end of town.

Prediction #7: Given that Microsoft has not been putting as much effort into Access, my feeling is that Excel’s capabilities in this area will be enhanced to the point where it can take over from Access. So many people use it like a database already anyway, why not give them a few extra tools and make it official?

In-app Purchases
If Apple can do it, why not Microsoft? Apple have found a way to generate huge revenues by making it easy for developers and consumers to connect. Microsoft could do the same so that developers could sell templates, add-ins and other customisations right there inside the MS Office products. Given the ubiquitous nature of Excel, there would surely be a huge market for innovative apps for business.

Prediction #8: The current Office Marketplace will become far more iTunes-like.

Summary
While I don’t have a special hotline to Microsoft I think Excel could be so much more! These are just my predictions … please don’t think that I’ve somehow gotten my hands on an alpha version of Microsoft Excel 2020!

They are just a few things that seem both likely and desirable to me. But if you know someone at Microsoft who works on Excel, feel free to forward a copy of the article to them.  Then, if they adopt some of my suggestions, I’ll look REALLY clever!

 

Recent posts by Jeff Robson

Comments for “The Future of Excel: 8 Bold Predictions!”

  1. Gavin Townshend says:

    Very interesting. I certainly hope these predictions do come true.

  2. John Catsicas says:

    With respect to Prediction 7, my thoughts is that Excel remain an analytically engine (improve the Charting capability with offering more types of Chars) and possibly make it easier to use Access (keep it independent.

    My Prediction is that Microsoft buys out Tableau and brings that rich graphical functionality in

Comment on this Article