Even if you’re not a teacher, be a teacher. Share your ideas. Don’t take for granted your education. Rejoice in what you learn and spray it.

Source: 9 Life lessons
Tim Minchin

Selecting a Formatted Table in a Formula

You can select a formatted table when you have a cell or range selected in the table by pressing Ctr + A. But that shortcut won’t work when creating a formula that refers to a formatted table.

To select the table in a formula you must click a cell in the table and press Ctrl + Shift + Spacebar.

Excel Hyperlink Formula Solution

Hyperlinks in Excel are a great way to navigate around a file, but they can be easily broken. Try this solution using a formula to create a hyperlink that doesn’t break so easily.

In the image below there is a formula in cell C3 that creates a hyperlink to cell A1.

Here is the formula.

=HYPERLINK("#"&CELL("address",A1),"<link text>")

Simply change the cell reference from A1 to whatever cell you want to link to. This works for cell in the current sheet.

Hyperlinks to other sheets

In the image below is an example of a link to another sheet.

The formula is.

=HYPERLINK("#"&CELL("address",Report!A1),"<link text>")

Again, change the reference to create a hyperlink that doesn’t break if the sheet name changes.

Pro Tip

To return after following a hyperlink press in sequence, function key F5 and then press Enter. Don’t hold them down just press F5 then press Enter.

Olympic Average in Excel

Averages are affected by outliers. If Bill Gates walks into a room the average net worth per person jumps substantially. In the Olympics some sports deduct the top and bottom scores before calculating the average score. Here’s a formula to do that in Excel. You need the subscription version of Excel for this solution.

Double Click the Excel Icon

You can close Excel down (with multiple files open) by double clicking the Excel icon – top left of screen.

This works for the other Office apps too.

If you haven’t saved a file Excel will ask if you want to.

To close a single file down use the X on the top right of screen.

 

Using Emojis in Excel Formulas

You can use conditional formatting to insert symbols in cells. You can also use formulas with emojis. using range names makes it even easier.

To insert an emoji icon in a cell you can use press the Windows key and the full stop.

This opens the Emojis dialog box.

In this example we are going to insert three separate symbols in formulas.

I have named each cell that has an emoji. A1 = Tick, A2 = Cross and A3 = Dash.

You can use these names in formulas throughout the file.

The formula in cell F2 (Sales) is.

=IF(D2>E2,Tick,IF(D2<E2,Cross,Dash))

The formula in cell F3 (Costs) is.

=IF(D3<E3,Tick,IF(D3>E3,Cross,Dash))

The advantages with using formulas instead of conditional formatting is that you can format the cells. Plus using formulas in cells is easier than using formulas in conditional formats.

Naming your emojis makes then easier to use. You can use these emoji icons names in your formulas throughout the file.

Clear all formats

To clear all the formats for a cell or a range select the cell/range and press in sequence Alt H E F don’t hold the keys down.

All the formats will be removed.

If you need to see the underlying number for a date this is an easy way to find it.