Cooking with Excel, A 'DIY' approach for Beginners, to Learn from Home

The Dilbert strip has always been a favorite. Couldn't resist citing for this article. This was a simple training I conducted some years back, to inspire self learning. Could be relevant for most beginners. I have tried doing this over complex spreadsheets as well, and beginners always loose interest.

Lets get started. What if, one fine day, you figure out, that you want to eat a Tartiflette. So let’s learn to make one!

Tartiflette with sweet potatoes

Ingredients

A traditional tartiflette is made with ordinary potatoes but this recipe uses a mixture of both the sweet and ordinary variety.

1. 1kg waxy potatoes, peeled and cut into 2cm cubes
2. Salt and freshly ground black pepper
3. 1 tablespoon vegetable oil
4. 20g butter
5. 2 onions, peeled and finely chopped
6. 750g sweet potatoes, peeled and cut into 2cm cubes
7. 150g cubed pancetta
8. 1 teaspoon freshly grated nutmeg
9. 500ml crème fraiche
10. 1 whole reblochon cheese, about 450g

Method

? Put the ordinary potatoes into a pan of salted water; cover and bring to the boil and cook for 5-6 minutes until just cooked through. Drain well and set aside.

? Heat the oil and butter in a large frying pan and fry the onions over a low heat for 5 minutes, stirring to prevent them colouring. Add the cubes of sweet potato, mix well, then cover and cook for 15 minutes. Stir regularly to prevent the potatoes from burning. The sweet potatoes should be just cooked through.

? Heat the oven to 180?C/mark 4. Add the pancetta cubes to the frying pan, mix together and cook for a further 5 minutes. Stir in the plain cooked potatoes and sprinkle over the nutmeg and season with pepper and a little salt if needed. Transfer to a large ovenproof dish or roasting tin.

? Gently warm the crème fraiche through and pour over the potato mixture. Cut the reblochon cheese into thin slices and arrange these over the surface of the potatoes. Bake in the oven for 25 minutes until golden and bubbling. Serve with a simple side salad."

If you notice the Bold parts above, these are either known by some of us, or we just need to search the internet and get an understanding. Rest rests-upon how meticulously we can follow the series of instructions and voila! we have a Tartiflette.

Now let’s look at the skills which were required to perform the above feat!

Skills Required for Cooking

1. To figure out what one wants to eat.
2. Finding the Recipe.
3. Reading and interpreting the detailed steps.
4. Perform the written instructions in a practical way.
5. Follow Cleanliness and Hygiene.

And you are done!

 I believe working on Excel is very similar to cooking. Let’s say, one fine day, you want / have to Work on an Excel sheet.

Skills Required for Excel

1. You just need to figure out what you want to achieve.
2. Search the functions (formula) which would be required to achieve the requirement.
3. Learn how the function is applied / used.
4. Apply these functions correctly.
5. Keep the workbook neat and clean.

And you are done!

However, the most difficult part above could be to figure out what to do. Let us decode this a bit with an example.

Accounts team are typically the ones who use a lot of spreadsheets. Hence, above is a typical example of a Manpower capacity utilization report. How about you spend a minute looking at the sheet and figuring out how to prepare it in a Good way.

Here are some steps which could make the "figuring out" part a bit easier.

1. Spot all the Logic involved one at a time.
2. Address each logic and find the best way to compose it.
3. Keep an eye on Logic which are dependent on each other.
4. An ideal Excel sheet should minimize Human efforts required to manage & change data on it.
5. In short, the Sheet should auto update as soon as direct or linked data changes.

Above I have highlighted just the simple points that could be considered to improvise upon this sheet. "Number of Working Days" for an example should always be calculated in an automated way considering even the holidays. So what would you search on the internet to find this formula? Of-course "Number of Working Days in a month Excel".

If you could think of adding the word "month" before your first search, it means you are able to think through in a comprehensive way. You do need to add the word excel as well to keep things in context. For the "Duplicate Data" point, ideally we should try and keep repetitive data out of picture and direct all formulas to the first instance instead. To be able to Drag and Auto-Fill sections, we need to carefully use the "$" in the cell locations. All of this is always available 24 x 7, online.

However all of the above could just be the tip of the Iceberg, there is so much more that can be done by possibly looking at how the data in the above sheet is brought in, interface with other Workbooks etc. It is more or less always about our ability to think through the intent, content and the method. And less about the formulas one knows!

So are we done after we have used the right formulas to facilitate an efficient operation of the sheet?

Well, going back to the cooking example, do you like to eat food, if it is served like this?

Obviously we need to work on the formatting and the look and feel of this Sheet. It should be easy for anyone to spot the different data on it and focus on the ones which needs attention.

Similarly, would you call someone good in Excel if he/she knows a lot of formulas?

Well, in that case you are a good cook if you know about a lot of food ingredients!

It’s not about the Formulas one knows, but the ability to deduce a logic and use the formulas, to create a spreadsheet which minimizes human efforts to manage, revise and operate the data.

However, the above is just my opinion & perspective.

And remember, never cease to Excel!

Thank You,

Animes