Excel Basics- The juicy Bits

So you know a bit about Excel, maybe you use it at work or you’ve just started to use it and know the basics?

But you know that there is probably tons of stuff you can do with Excel but don’t realise!

So here are some bits and bobs that I’ve gathered over the years, have a look and hopefully you can find a little something that will help you in your quest to become an Excel Master.

  1. Conditional Formatting
  2. Comments
  3. Add a drop down list
  4. Transpose
  5. Select a range using the keyboard
  6. Remove Duplicates
  7. Excel Formulas and Functions
    1. Formulas
    2. Functions
  8. Index Match- the smart lookup
  9. V-lookup Tips
  10. CONCATENATE Function – joining things
  11. MID Function

Excel Conditional Formatting

We can format cells with a particular style if the contents meet certain criteria.

For example, when adding the word ‘Y’ to a cell, we can set the style to change to Light Green fill, green text colour and bold. Here’s how to do it:

  • Select the column, then in Home tab, click Conditional Formatting. In the dropdown, select the required condition criteria. In this instance it is:
    • Highlight Cell Rules -> Text That Contains
    • In the ‘Text That Contains’ dialog box, type ‘Y’
    • In the right format box drop down – select Green Fill with Dark Green Text
    • Press ok

Now every time you type ‘Y’ in that column it will be changed to this formatting.

Another example – when adding the word ‘N/A’ to a cell, we can set the style to change to grey, italic. This helps the viewer to read the spreadsheet easier:

  • Select the column, then in Home tab, click Conditional Formatting. In the dropdown, select the required condition criteria. In this instance it is:
    • Highlight Cell Rules -> Text That Contains
    • In the ‘Text That Contains’ dialog box, type ‘N/A’
    • In the right format box drop down – Custom Format
    • In the Format Cells dialog box, select font – Font Style (italic) Color: Grey, border and fill requirements
    • Press ok

Now every time you type ‘N/A’ in that column it will be changed to this formatting.

To create a new rule, clear a rule or manage rules, go to Conditional Formatting button and select the required from the list.

You can be really fancy with your own bespoke conditions, such as using a formula to determine which cells to format.

Conditional Formatting is a great way to help you to organise your data within a worksheet!


Excel Comments short-cuts

Insert or edit a comment in Cell

Shift + F2

Delete a comment in Excel

Shift + F10, M

Show all comments

Alt + V, then C

Hide all comments

As above

In Excel you can add a drop down list to a cell which makes it easy to assign pre-determined cell values. This is called DATA VALIDATION. Here’s how you do it:

  1. Have your list (in another worksheet (hidden), or anywhere on the current worksheet.
  2. Select your drop down cell.
  3. Go to the Data tab and select Data Validation > Validation Criteria > List
  4. Then select the range (Source), and press OK.

Transpose a row of data to make it a column of data in Excel

  1. All you have to do here is select the row and copy. (You would usually want the top column heading as well), thus:
  2. English French German Spanish Italian Vulcan
    Hello salut Hallo Hola Ciao ponfo mirann
  1. Then place the cursor where you want the column to start (on a spare worksheet maybe)
  2. Right click – paste special -> Transpose
English Hello
French salut
German Hallo
Spanish Hola
Italian Ciao
Vulcan ponfo mirann

This is helpful when you want to read a list of data from top to bottom (instead of left to right. (You can read more within the monitor and spend less time scrolling.)


Selecting Ranges and Data in Excel using the Keyboard

Excel – Select a range on a worksheet using the keyboard

  • Place cursor at the top left corner of the range.
  • Press Shift (and keep it down) + Arrow Down. This will select each row in the leftmost column moving downwards. Keep the Shift button held down…
  • (Shift) + Right Arrow to select all rows and columns in the range.

You now have all cells in the range selected.

Excel – Select ALL data in a worksheet using the keyboard

  • Place cursor at the top left corner A1.
  • Select ALL rows with data by pressing Shift + Ctrl + Arrow Down – keep Shift + Ctrl pressed down…
  • (Shift + Ctrl) + Right Arrow – to select all rows and columns with data.

Note: Remember that this technique will not select blank rows so if you have any, you’ll need to delete them first.


Remove Duplicates in an Excel column

  1. Select the range in a column and copy.
  2. In another worksheet, paste, then whilst the range is still selected, in the ribbon go to DATA > Remove Duplicates, OK.
  3. The duplicates have been removed.

(Note: If you have a header in your selection you can tell Excel to ignore the header when removing duplicates).


What are Formulas and Functions in Excel? A Formula in Excel is an expression which calculates the value of a cell whereas Functions are predefined formulas which are readily available in Excel that do specific tasks.

Excel Formulas

A formula performs calculations or other actions on the cells in your worksheet. You can type formulas in active cells. As you type, it will appear in the formula bar. (So you can type from here also.) A formula always starts with a “=” which is followed by numbers, mathematical operators, cell references and Excel functions.

=2*3+5 (calculation using numbers)

=A1+A2 (calculation using cell references)

=Sheet2!B2 (Uses cell references from another worksheet on the same workbook)

=range1+range2 (calculates using named ranges for cell references)

=SUM(B4,C4) (Uses an Excel function (see below) to calculate cell values

Excel Functions

Excel massive range of functions make it easy to calculate results for specific tasks. I will only list some of the main ones that I use often. You will find some people go their whole lives without using most of them, but some functions are quite niche. If you are an Excel wizz you will be able to create your own functions using Excels build in scripting language VBA.

How to add an Excel Function:

  • Click the ‘Insert Function’ button (fx)(next to the formula bar) to open the formula wizard. Search for a function and select it. A dialog box opens allowing you to enter the arguments.
  • You can easily change from absolute to relative cell references and vice versa by selecting the cell that you want to change, in the formula bar, select the reference that you want to change, Press F4 to switch between reference types.

How to set up Index Match. The smart way to lookup.

Index Match allows you to lookup values in a table based off of other rows and columns, in any direction. It’s more powerful than VLookup. It is actually two functions used together. Here we break them down to understand how they work.

Excel Index function

Index takes a cell range and returns a cell within that range, based on a count. It looks like this:

-INDEX(range, row-or-column-count)

For example,

=INDEX(A1:A10,10) will select the tenth cell value within the range.

Excel Match function

The Match function returns the position of a cell value within an array by matching against a criteria string. Here is the formula:

=MATCH(find_this_value, in_this_range, match_type)

A B
1 Driver Number of championships
2 Lewis Hamilton 3
3 Nigel Mansell 1
4 Jensen Button 1

So, =MATCH(“Jensen Button”, A1:A4, 0)

Will return 4, because the string is found at the 4th cell from the start of the range.

Note: Keep match_type as 0 for the time being.

Note that the range can be horizontal or vertical!

So, how do we combine INDEX and MATCH to replace VLOOKUP? We feed the MATCH function into the row_or_column argument of the INDEX function thus:

=INDEX(range, MATCH(lookup_value, lookup_range, match_type))

I’m thinking a diagram might be better:

Excel INDEX MATCH diagram

Excel INDEX MATCH diagram

Hopefully this diagram of Excel INDEX MATCH in action will help to understand.

The diagram show the action as similar to how you would do a VLOOKUP – but with INDEX MATCH you can go forwards or backwards up or down!

Here are some of the benefits of INDEX MATCH over VLOOKUP:

  • You don’t have to count to work out which column you need to pull from
  • You can insert columns. Unlike with VLOOKUP, where the formula will break.
  • You can lookup in any direction. You can pull from any column you want to.
  • Enhanced functionality with INDEX MATCH MATCH to look up across both rows and columns, or use an INDEX MATCH with multiple criteria.

V-lookup Tips

So you already know how to do a Vlookup but find it cumbersome? Reading these tips will make creating Vlookup happen in minutes.

  • Add the lookup table on a different page in the same workbook (you can hide it later).
  • On the master table set all numerical lookup values to ‘text’. On the lookup table, make sure all cells in column A are set to ‘text’. This is because if excel is looking for a SKU that is numerical ie 100-01, it is best to set numbers to text. This creates a common ground and avoids confusion. If you ask excel to find a text number it will fail to find it if it is set to a numeric value.
  • Name the range! Select the lookup table range, right click in the selected range and click ‘Define Name’. Type a simple name for the range ie ‘bcdb’. This way when you create the Vlookup, in the section to define the range all you have to do is type the name of the range. Easy!
  • If you will be dragging your Vlookup formula from one cell to another, make sure you set absolute cell references where required.

CONCATENATE Function – Joining things together

Yes you can join things together – cell values, text etc. In programming, this is called concatenation. Great word – makes it sound complicated but it’s really not!

Formula syntax:

CONCATENATE(text1, [text2], …)

Note: for simple concatenation use the logical AND operator instead: =A1 & B1

Here you can create print formats by joining cell values and text strings:

$A$1 = CJ (Label stock)

$B1 = 060 (Label format)

$C1 = 001 (Database record number)

CONCATENATE($A$1, $B1, “-A ”, B1)

Will create: CJ060-A 001

(Note the space after “-A “).

So now you can drag and copy the formula using absolute and relative cell references and create a list of print codes with different database record numbers.


MID function

With the MID function you can find and select characters of a string starting from a certain point within the string.

Example:

1

A

B

2

1011061058604384017210523101234567

10610586043840

Where A2 has a named range of ‘VAR’.

The formula in cell B2 is =MID(VAR,4,14)

So the MID function is selecting the range VAR in A2, it then moved to the forth character and then returns the following 14 digits (shown in red).