Xlteq providing Excel Spreadsheet tips.
- Inserting an object
- Equations
- Internet information
- Scenario Manager
- Goal Seek
5 Examples of Excel’s lesser used functions
Inserting an object
From time to time, when creating a spreadsheet, you may want to include additional information, instructions or supporting documentation using Word or Power Point. Perhaps you need the user to populate another spreadsheet or maybe you want to add in a picture or sound clip. You can provide a link to the document or provide these as separate documents but there is a tidier solution and that is to insert the object inside the Excel workbook. This means that wherever the excel workbook goes, the extra objects go with it.
The process is very simple and to find the Insert Object option you simply need to go to the “Insert” menu
And then select the “Object” option
This brings up the Object form where you can create a new object in Excel or insert one from a file location
Whether you choose to create a brand new object insert one which has been already created, you have to decide how it appears in Excel using the “Display as icon” tick box.
The following image shows how a word document looks when it is inserted (on the left) compared to how it looks when it is created as an icon (on the right)
NB some objects will appear as an Icon even if you do not tick the box
To edit either one simply double click on the image or icon. In this example it will create an instance of Word inside Excel which will allow you to edit the document.
If you want to insert a new object then the above is all you need to know. However if you want to insert an object which already exists then there are a couple of other considerations.
The “Display as icon” option remains but another option, “Link to file” is now also showing.
Using this new option, if it is NOT ticked, you will create a COPY of the file and embed it into the Excel work document. However if it IS ticked then you simply create a reference to the file. So if you do this then make sure anyone who needs to access the file also has access to the folder where it is stored. Also note that linking the file will allow them access to the file, so they may be able to change that file if they have permission to do so.
Equations
You might be using Excel to perform some maths equations. However trying to display these equations can be a bit tricky. Fortunately Excel has a couple of ways to make this easier.
Under the Insert menu option is the “Equation” option
This provides a list of predefined equations to choose from and 2 ways to manually enter them found at the bottom of the list
If you choose the first option “Insert New Equation” then a new tool bar opens up which allows you to enter the equation from a list
Alternatively, if you select “Ink Equation” you can manually draw the equation and let Excel transform what you write into characters (and you don’t have to be a good writer for Excel to understand the equation as the following image proves).
Internet information
You can get some types of data from the internet quite simply from the “Data” menu
What follows are the three default sets of data available to you (although this may vary depending on your version of Excel). You will need to be connected to the internet for the following to work.
It does not cover every functionality available, however provides enough to get you started.
Currency Exchange Rates
As an example, in one cell we type “GBP:EUR” and in another “GBP:USD”. We then select both these cells.
From the “Data” menu we choose the “Currencies” data type
And you can see the cells have changed.
If we click on the little “house” icon in one of the cells then you will now see additional information on the exchange rate for that currency.
Countries
Alternatively if you enter a list of countries and click the “Geography” button you get information on the country
Notice that it was able to work out that the three countries we entered (USA, America, United States) were all the same country and has changed the displayed list of countries appropriately (ie United States)
Clicking on the foldable map displays data on the selected country
Stocks
To get stock information you will need to know the stock exchange code (eg for the London Stock Exchange it is “XLON” and for the NASDAQ it is XNAS) as well as the stock code you want to check (eg for Microsoft it is MSFT). Enter these values into a cell (separating the stock exchange code and stock code with a colon) and select the cells.
Then select the Stock button to convert these into stock information
Default textClick on the house icon to display information on the stock.
Scenario Manager
Imagine you have created a business plan in Excel for the forthcoming year with expected sales and forecasts. However a lot of these values are unknown or at best, guesses, so you want to be able to create different scenarios to understand what happens under different condition. The problem is if you have a complicated workbook with multiple sheets and formulas, then you don’t really want to duplicate all these sheets just to be able to change a few numbers as this will create bloat and if you need to correct a mistake or add something on one sheet, it means you will need to make this change on all the different scenarios you created.
For this solution we can use Excels “Scenario Manager” found on the data tab under “What-If Analysis”.
For this example we create a very simple scenario with Sales, Staff Pay, Rent and Loan information being entered values and Profit being a calculation of these 4 values.
We now want to create “What-if” scenarios, so we select the “Scenario Manager” option to bring up the form.
As we have already entered values into this scenario we will first all of create a “Basic” scenario. So we click on “Add…” to bring up a new form and enter a scenario name of “Basic” (although you can call this what you want)
We now need to define which cells will change in this scenario, so we press the cell selection button and select the 4 cells where we entered data
So in this example the form will now display this for the cells which will change 
When we click on “OK” we are presented with a list of the cells we selected and which will be pre-populated with the current values of the cells.
We click “OK” and the scenario is created. 
If we now want to create a new scenario (for example what if we get fewer sales than expected) then we can change the spreadsheet to reflect this and create a new scenario following the instruction above.
For example we reduce the amount of sales from 100,000 to 95,000 and create a new scenario called “Reduced Sales” using the above instructions. 
Now it is a simple case of using the scenario manager to display which scenario we want to look at. Select the scenario, press the “Show” button and Excel will update the cells for you.
Goal Seek
This is s simple function Excel has which can help us to find out a value which will produce a specific result
In the below example we enter a value of 200 and we have a formula which simply multiplies that cell value by 4 (to give a result of 800)
Lets say we want to get a result of 978. We could try and enter values manually until we get to 978, however there is an easier and quicker way.
Under the “Data” menu options, under the “What-if Analysis” option, we choose “Goal Seek”
In the form which pops up we enter the cell reference to the cell we want to get the answer for (in our example it contains the formula which multiplies the number by 4). We then enter the value we want to get to (in our case we want 978) and finally enter the cell reference of the cell which can be changed (in our case it is the cell reference we initially entered 200 into).
Excel will then work out the what input value is needed to achieve the result we want (or gets as close as it can) 