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.
(more…)You can close Excel down (with multiple files open) by double clicking the green 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.
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 =…
The typical range reference looks something like A1:A10. You always refer to the top left cell followed by the colon followed by the bottom right cell of the range. Did you know Excel can handle you entering the last cell followed by the first and it corrects it for you?
(more…)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.
Often when you import data into Excel the dates may include a time. This can make summarising the data more difficult as time is included as a fraction of a date. You can use an old function to fix the problem. This solution requires the subscription version of Excel.
(more…)
Not sure when it happened, but a recent update to Excel has enabled the IFERROR function to handle spill errors.
(more…)