Excel Training – Page 70 – STL

One of our trainers showed me this function when I was trying to find data in a huge worksheet. It is a fantastic function for List Management and saves me lots of time and stressing.

Here is a simple example to demonstrate how to use filters in Excel.

I have my worksheet with details of budgeted items for a business trip. I want to try and identify the one-off items to see if I can reduce those costs.

Here is my worksheet with details of the items and units. I want to filter the results by cost per person.

budget-with-per-unit-excel-training-courses — Here is my budget worksheet with the full list of items and costs.

I can filter the items by the ones that match my search criteria, by using the the Filter option in the Data tab.

Data-tab-filter-excel-training-courses — The Data tab has the Filter option ready to go…

To filter my budget, I put my cursor on the heading for per unit, and then click on the Filter icon. I know the filter has been applied as down arrows appear next to the heading titles.

filter-applied-headings-excel-training-courses — Filters applied, and to show me that it’s ready to go, here are the little down arrows.

I select the down arrow next to the per unit heading

filter-options-excel-training-courses — Filter options available to mix and match

I select the per person filter, by removing the tick from select all and then tick per person, and click OK. Here are my results.

filter-per-person-excel-training-course — The filter results confirm two items.

When I want to see all my items listed again, I click on the down arrow next to heading and select Clear filter from per unit.

remove-filter-excel-training-courses — I can return to all my items by removing the filter.

Remember: It is possible to forget you have the filter on, and then have a minor moment of panic when you think you’ve lost data…I think Excel knows this can happen….so look out for the down arrow next to your heading as a reminder. Excel is thoughtful like that!

Using filters is included in our Excel Intermediate courses under List Management. For more information on our Excel training courses and the course syllabus, please see https://www.stl-training.co.uk/excel-2010-intermediate.php

If you are planning a budget for a trip involving two different currencies, fluctuations in conversion rates can create more work for you. The solution is to use a formula that automatically updates all the results if the conversion rate changes.

At the top of my worksheet, I have added a currency conversion rate. I know that today my £1 buys me €1.24.

Tip: to add the € symbol, hold down the the ALT Gr button, located on the right of the space bar, and press 4. This will add your euro symbol

I now type in the details of my budget, and then calculate the total cost from pounds to euros using the conversion rate at the top of my worksheet.

The trick is to use the ‘$’ symbol in the formula, on the cell reference containing the conversion rate. This is known as an absolute reference.

Tip: Use format painter to copy the euro formatting in B2 into the columns with euro costs. Click on B2 then click and drag over the cells you want to apply the format to.

So my worksheet will look like this:.

currency-conversion-excel-training — The conversion rate formula uses today’s rate of 1.24 Euros, in Cell B2. If the conversion rate changes, I can amend B2 and the formulas in column F will automatically update.

Later in the week, the conversion rate changes to €1.35. I don’t have to manually adjust all the figures in the Total cost in £ in column F – I update cell B2 with the new rate and all the figures in column F will be updated automatically.

formula-excel-training-conversion — The formula view of the conversion rate and total cost in £s

So all my figures are updated and my budget now looks like this:

Conversion-rate-change-excel-training — The exchange rate change has been applied to B2 and the budget in £s has now been automatically updated.

I find that my formula today is Excel + training = useful skills!

This is a simple formula with a practical application and it save time as you update one cell for an impact on many calculations. To build on your understanding and use of formulas at all levels of Excel , have a look at https://www.stl-training.co.uk/microsoft/excel-training-london.php.

Blog Categories