What If ... Practise Makes Perfect In Any Excel Scenario

Fri 17th February 2012

It's a problem for many Excel users: we've been on the course, completed the progress report and reviewed our learning. But back in the office, the what-if scenario that made perfect sense in the training room suddenly sounds like a 64-thousand dollar question; and one that you can't resolve. If you're struggling to put Excel into practice, then it may be time to let the trainer come to you.

Walking through the office recently I heard a common solution to a typical Excel query: "Just ask Hannah, she's an Excel wizard." Now, I know for a fact that Hannah does not sit at her desk wearing a pointed hat with a wand in hand waiting to answer colleagues' queries about formulas and conditional formatting; nor is she a virtual assistant, primed to start up Excel's Query Wizard.

Query Wizard, incidentally, can be used to address most Excel questions. The Query Wizard helps you to find and combine data from different tables and fields in your database.

An inner join (a query operation that specifies that rows from two tables are combined based on identical field values) is created automatically when the wizard recognises a primary key field in one table and a field with the same name in a second table.

You can use the wizard to sort the result set and for simple filtering. When finished querying, you can choose to return the data to your Excel document, or refine the query further in Microsoft Query.

To use the Query Wizard, simply go to the Data tab, and in the Get External Data group, click From Other Sources, and then click From Microsoft Query. In the Choose Data Source dialog box, make sure that the Use the Query Wizard to create/edit queries check box is selected. Now double-click the data source that you would like to use.

However, I digress. Like Hannah, I find it both fascinating and compelling to try to find the answer to a query and to share this knowledge with colleagues who might be stuck with the syntax of spreadsheets. Sadly for us, this is not Hannah's full-time job in the office and she is not paid to solve other people's Excel conundrums. And one of the most common queries Hannah seems to need to answer is the what-if analysis.

By using what-if analysis tools in Microsoft Office Excel, you can use several different sets of values in one or more formulas to explore all the various results.

For example, you can carry out what-if analysis to create two budgets that each assumes a certain level of revenue. Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result.

Excel provides several different tools to help you perform the type of analysis that fit your needs. What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet.

There are three types of what-if analysis tools that come with Excel: scenarios, data tables, and Goal Seek.

Scenarios and data tables take sets of input values and determine possible results. A data table works only with one or two variables, but it can accept many different values for those variables. A scenario can have multiple variables, but it can accommodate only up to 32 values.

Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result.

In addition to these three tools, you can install add-ins that help you perform what-if analysis, such as the Solver add-in. The Solver add-in is similar to Goal Seek, but it can accommodate more variables. You can also create forecasts by using the fill handle and various commands that are built into Excel.

I'm sure that most organisations have a helpful Hannah in the workplace, colleagues who we rely on to come to our aid each time we are stuck on spreadsheet formula. But it's really up to ourselves to empower our own learning and to feel confident about using the software on a daily basis.

If you are about to embark on installing a new version of Excel, and you currently rely on a Hannah to help with everyday spreadsheet queries, then it's probably a good idea to opt for on the job training. This way qualified trainers can be on hand to assist and instruct users while they are at their workstation.

This means that immediate answers and solutions can be given to those repetitive everyday tasks and problems - without the need to disturb Hannah. With a bit of practice and professional guidance, anyone can be a wizard with what-if .

Author is a freelance copywriter. For more information on advanced excel training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2051-what-if-practise-makes-perfect-in-any-excel-scenario.html