# Excel 2007 training – workbook protection

If you ever need to send an Excel workbook by email to someone else, you may want to hide certain worksheets. When they receive the file, you want to ensure they cannot unhide any hidden worksheets. After attending an excel training course you may only learn the skills of how to protect a worksheet with a password.

The first step towards protecting the workbook is to hide the required sheets so they are not in view. After that go to the Review Tab on the ribbon:

Click on Protect Workbook and then type a password in:

Once you have entered and confirmed the password click ok. If you right click over one of the Worksheet tabs, the Hide and Unhide options will be greyed out. The password would then be needed to obtain access to the hidden sheets.

# Excel Courses – Retrieve Data using the Index and Match Functions

If you have an Excel Worksheet that contains Data relating to the hours of training for each Employee for Microsoft Excel Training London on Monday, Tuesday and Wednesday, you may want to return the hours trained for a particular employee on a particular day in another Excel Workbook or Excel Worksheet.

The spreadsheet shown below is the data set we will use:

Data Table Excel Workbook

The Excel INDEX function can return an item from a specific position in a table of Excel data using Row and Column headings. The MATCH Function can return the position of a value in a list. The INDEX and MATCH functions used together provide a flexible and powerful method for extracting data from a table.

We start with the INDEX function. The Index function has three arguments(Array,Row_Num,[Col_Num])

The Array argument is the range of cells we are trying to retrieve the data from. The Row_Num argument is the Row you are retrieving from and Col_Num the column you are retrieving from. The value at the intersection point of the Row and Column arguments will be returned from the cell range from the Array argument. We use the MATCH function to assign the Row and Column to the second and third arguments of the INDEX function.

The Match function has three arguments:

(Lookup_Value,Lookup_Array,[Match_Type])

Here is the spreadsheet we will use to create the formula which will return the number of hours. The formula is entered into cell B5 of the Index and Match formula sheet:

Index and Match formula sheet

The screenshot below shows the result and notes:

=INDEX(‘[Data Table Excel Workbook.xlsx]Sheet1′!\$B\$2:\$D\$5,MATCH(B2,’[Data Table Excel Workbook.xlsx]Sheet1′!\$A\$2:\$A\$5,FALSE),MATCH(B3,’[Data Table Excel Workbook.xlsx]Sheet1′!\$B\$1:\$D\$1,FALSE))

Enter a value into B2 and a value into B3 of the Index and Match formula sheet and in cell B5 of the Index and Match formula sheet it will return the hours of Excel Training completed for that Employee.

Formula Explanation Notes:

1. The first argument of the Index function relates to just the hourly figures in the Data Table Excel workbook(B2:D5).

2. The Row Argument of the Index function uses the Match function to match the value of cell B2 in the Index and Match Formula sheet to the corresponding value in the Data Table Excel Workbook(A2:A5).

3. The Column Argument of the Index function uses the Match function to match the value of cell B3 in the Index and Match Formula sheet to the corresponding value in the Data Table Excel Workbook(B1:D1).

4.The formula returns the value form the Array argument that represents the row and column values specified.

5. The FALSE statement returns the exact value found.

# 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.

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.

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.

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

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.