If you ask most of the billion Excel users around the globe, most of them will say they use Excel for these two main reasons:

• As a convenient place to store information – more structured than using post-it notes, but flexible enough to evolve over time
• A convenient way to exchange information – because everybody else uses it

If you are an Excel power user, you'll add a third reason:

• You use Excel to process important information. And your organization depends on the knowledge you have in your head to get the process right

In working with Excel, you and your colleagues are using it as the core engine to drive your business. Let’s look at a couple of real-world examples.

Excel as the engine room of the business

There are many important jobs in every business (I’m using business as shorthand for any organization). But those jobs will grind to a halt without a way of capturing, processing, and...

One core promise of this book is to build on your existing knowledge of Excel and help you find solutions that are more scalable, robust, and supported by a wider team. By this, we mean the following:

• Scalable: Whatever we build can be used by tens or hundreds of users simultaneously, manipulating a very large dataset.
• Robust/durable: What we build can be left to run by itself, since it won’t easily break and highlights any errors using tests that can be run automatically.
• Supported by a wider team: Most Excel sheets are maintained by only one person. What if they leave? The solutions we build should be understood by many people.

Typically, these are called enterprise solutions. In your organization, you’re likely to have several of these, supported by an internal or external IT team. Let’s look at a couple of examples.

Examples of enterprise solutions

We gave an example before of a company using Excel to track...

We talked earlier about Excel being the engine room of many businesses. Somebody (a client or an employer) pays you to use it for a reason. The knowledge you have embedded in your spreadsheets helps the business save money or make more sales. If you work with an organization that saves lives, the knowledge you have embedded in your spreadsheets could get people the right medicine at the right time.

The knowledge you have embedded is the important part. Spreadsheets are just convenient tools to capture and share that knowledge. Since most businesses have intense (and growing) competition, how well you capture and manage that knowledge dictates whether your business will survive and grow or shrink and close in the coming years.

Spreadsheets are convenient tools, but they are not perfect. In fact, if your organization relies heavily on Excel, it is likely that most people haven’t (yet) thought about how to capture and maintain the...

On a bad day at the office, you've probably had the great idea of either cloning yourself or one of your key team members. That way, you could leave your clone working away while you do more important things, or just take a break at the beach.

Just to be clear, this book does not recommend cloning humans as a practical solution!

What is possible is applying recent advances in AI to the problem. Surely if we live in a world of almost self-driving cars, we could train a computer to capture and process the knowledge in our Excel sheets, in a way that can solve many of the problems that we’ve talked about?

Will AI take my job?

You may read this and think, if a computer can do my job, what am I going to do? With almost every bit of automation in the last 200 years, about 80% of your job can be automated, and it’s normally the boring and repetitive bits. If the boring bits are automated, it leaves room...

The preceding business rule examples deliberately used a when ... then format. This was no accident; the format gives clarity about the knowledge we are trying to share with the AI system.

The when … then format

Let’s highlight the key format for writing business rules:

When "something is true" then "take some action"

This is a very simple concept, so simple that if you’re an Excel power user (or have programmed in other languages), this is something that might trip you up.

In Excel, the flow is important – one cell references another, which might reference a third cell – but the order in which the values will be calculated is guaranteed. Likewise, in computer languages such as Java or VBA, statements are executed strictly in the order they are written (with options to loop and call functions).

A business rule can activate any time the “when” part...

We use Excel so much that we don’t pause to think how powerful it is. While it looks like a single block, Excel can be broken down into many different components. Figure 1.8 shows one simple way of breaking out Excel into different parts.

Figure 1.8 – Excel broken into simple model, view, controller pieces

This pattern is a way of breaking up Excel into more simple parts, each with one key job to do:

• You give commands to Excel using the controller. Typically, this means clicking on a command in the toolbar, on the menu, or elsewhere on the page, which makes Excel do something. This something usually changes the model.
• The model is how Excel stores its data. If you’ve imported CSV or other non-Excel formats before, you may have a clearer idea of this. These formats are pure data, without any functions or VBA code.
• Excel can present this data as one or more views. When the model updates...

In this chapter, you learned that you are not alone in your frustrations with Excel, but that it is far too important of a tool to abandon. We looked at enterprise solutions that can solve some of the problems you are facing, but often these have an overly large gap to bridge before they are useful to you. Trying to bridge this gap, we looked at the core business problems we might use Excel for and discovered that AI, and in particular a rules-based approach, might be able to help.

One good surprise is that business rules are very familiar, and we might have used them already without knowing it. We discovered that Excel is actually a much more powerful tool than we give it credit for and that we don’t need to fully replace it, just upgrade certain parts – if we aren’t afraid to delve under the bonnet. We also mentioned that while other solutions are available, an approach based on business rules is a good investment since it has a large element of future...