Category: Excel Training

Categories
Excel Training

Excel Training: Quick method for Creating Named Ranges

Microsoft Excel has the Named Ranges feature that allows users to give a name to a cell or range of cells that can make it easier to refer to in formulas. The naming of the cells can be tedious if there are numerous ranges to be named on the sheet. Here’s a quick way of achieving multiple range names.

Select the whole range of cells that you wish to name. In the example below, we need to name each Quarter and each Department ranges.

Excel Named Ranges SheetIn this example we select A4:F9

 

 

 

From the Formula Ribbon, Defined Name Section, Click the Create from Selection button.

Excel Named RangeTick the checkboxes that relate to the range you would like Excel to consider for naming.

Ticking Top Row will create range names for each of the Quarter Columns, and the Left Column will create Department name ranges.

Click OK

 

The Ranges are created and named. They will appear in the Name Box (beside the formula bar).

Named Ranges microsoftNote: Named Ranges can be edited via the Name Manager option on the Formulas Ribbon.

Categories
Excel Training

Excel Training: Use Goal Seek for What If Scenarios

Excel has some great functions for “What if…” scenarios. One tool that can be used is the Excel Goal Seek. This allows you to choose any current formula and set the answer value to your own goal.

e.g. If you have a formula that works out how much profit you make from selling items, such as £20, but you would like it to be £30 profit, Goal Seek can change various cells to achieve that £30 goal.

Below is an example similar to ones we use on our Excel Training courses based in London. We have a simple loan calculator that uses the PMT function to work out monthly loan repayments.

Excel Goal Seek Tool

 

 

 

 

 

Open the Goal Seek from the Data Ribbon, Data Tools Section, What If Analysis.

Excel Data Tools Goal Seek Ribbon Bar

The Goal Seek dialog appears. Fill out as follows: Enter the cell you wish to set your goal to in Set Cell Field. Enter the figure you want the Goal to achieve in the To Value Field. Enter the variable (cell) you would like Excel to change to reach your set Goal in the By Changing Cell Field.

Excel Goal Seek ToolClick OK

The Sheet content will change to reflect the Goals set. You will be presented with another dialog box, Click OK to keep the changes, or Click Cancel to revert the cells back to original values.