This tutorial covers how to make table references absolute. It will cover both table column and cell references.
Download the Excel workbook to follow along.
Create a Picture Lookup in Excel
Many Excel users are familiar with lookup functions such as VLOOKUP, INDEX and MATCH to look up information in a list. But how about performing a picture lookup in Excel to return a picture dependent upon the contents of a cell.
This requires a little extra thought as a standard VLOOKUP is not capable of returning a picture from a list.
In this blog post, we will explore how to create a picture lookup. We will look at how to return the picture of a flag dependent upon the country name that is selected from a list.
Spreadsheet Setup
[Read more…] about Create a Picture Lookup in ExcelEssential Conditional Formatting Tricks
Conditional Formatting is an amazing feature of Excel. It is sure to create a spark of interest and questions during training. People see the potential in their spreadsheets and how easy their team could visualise their data and create engaging reports.
This tutorial looks at the two most requested Conditional Formatting tricks asked by Excel users.
Download the file used in this video to follow along.
Excel VLOOKUP Trick – No More Counting Columns
In this post, we look at a neat Excel VLOOKUP trick to select a column for the data to return instead of specifying a column index number.
This is one of the biggest frustrations for beginners to VLOOKUP and for users who work with wide data sets. Counting the columns to enter the correct index number is not ideal.
Well, this simple trick removes that hassle and will mean you only have to click the column now. Let’s see how to do this.
Watch the Video
Entering Fractions into Excel
Entering fractions into Excel can be confusing. When a fraction is entered, Excel converts the value to a date format e.g. 1/2 is stored as 01-Feb.
This is frustrating and without storing the value correctly as a fraction, any formulas dependent upon this value will not work correctly.
To enter a fraction in Excel, type a 0 and a space followed by the fraction. Excel then stores the number as a fraction and reduces it to the smallest possible denominator. So if you type 0 2/16, Excel stores it as 1/8.
Now with a fraction stored correctly the cell can be referred to within formulas without error. Further formatting can also be applied to the cell through the Format Cells dialogue box.
