Tag Archives: MM

Microsoft Excel 2010 – Extract Data From a Cell Using LEFT Function

In the following example there is a column of stock codes. The numbers represent our Supplier and the letters after the hyphen (-) indicates our Item number.

Excel Training LEFT Function

Suppose we need to extract the Supplier and Item codes and place in separate cells. If our Supplier codes were all equal length e.g. 3 numbers long, we could simply use the LEFT function.
In the Excel 2010 Training Courses here in London this is a common question.

Example:
Excel Training London Using the LEFT Function

Result:
Training Excel London Results of using LEFT Function

 

The problem we have is that not all Supplier codes are 3 numbers in length.
We can correct this by using another Excel function word, FIND.

FIND function can return a number value of where a particular character appears, e.g. if we have a word “BEST-STL TRAINING” the FIND function would tell us that the hyphen is the 5th character.

We could use this inside (nested) the LEFT function – But remember we do not want to include the hyphen (-) in our final result so we need to subtract 1.

Example:
Excel 2010 Training Learn to use FIND Function

NOTE: Without subtracting 1 in the above example, it would return a value of 4

Final Code: =LEFT(A2,FIND(“-“,A2)-1)

We could repeat this for the Item code by using a combination of RIGHT, LEN and FIND nested.

 

Excel Training – Retrieve Unsaved Excel Workbooks

Ever closed an Excel workbook and clicked the “Don’t Save” option, then realised you should have saved it! Microsoft Excel 2010 now has a file recovery option for just such an occassion. This has proved invaluable in our Microsoft Excel Training Courses in London.

Always check the AutoRecover time in the Excel options. To do this go to the File, Options and Save. (Shown below)

Set the “Save AutoRecover information every” time to 2mins.

London Excel Training Courses - Excel 2010 Options Dialog

Retrieving a workbook after it’s been closed without saving can be achieved by:

File Ribbon, Info

Microsoft Courses in London - File Menu

From the Info panel select the Manage Versions button, then Recover UnSaved

London Training Microsoft - Manage Versions Image

A list of unsaved documents will be seen. Select the one required.

Note: This works for both documents that you have previously saved, and docs never saved

 

Excel Training: Using Sparklines to display trends visually

One of the great new features of Excel that users enjoy employing during our Microsoft Excel Courses here in London is the Sparklines. These can be thought of as mini-Charts that reside in a cell, and can show a trend visually of a group of values.

In the example shown below there are sales figures from four store locations, London, Manchester, Birmingham and Liverpool. We want to see the trend of these figures visually beside each Department row, rather than create a separate Chart.

Excel Training - Sparklines

Start by selecting the range of cells required for the Sparklines. In the example above, Cells B6:E6.

From the Insert Ribbon select the Sparkline style required e.g. Line

The Create Sparklines dialog appears.

Excel Training in London - Sparklines Dialog

Select the location where the Sparkline should be displayed e.g. F6

Click OK – The Sparkline will appear in the chosen cell as seen below.

Excel 2010 Training - Excel Sparkline View

With the Sparkline cell selected you will see the Sparkline Ribbon – Design.

Excel London Training Centre - Excel Sparkline Ribbon

From this Ribbon you can chage the appearance and type of Sparkline e.g. from Line to Column or Win/loss.

Once the format has been completed you can copy the Sparkline to reflect other values, by copy/paste or using the AutoFill.

Sparklines are not visible in earlier versions of Excel (prior to 2010).

The Sparklines option will not be available if a Workbook is opened in Compatibility Mode.