# Excel’s Magic Percentage Symbol

I recently saw a post of LinkedIn (from Patryk Samborski) that used the percentage symbol with the SEQUENCE function to produce a list of decimals and I thought I would have a play with that idea.

“It is not how much we have, but how much we enjoy, that makes happiness.”

Charles Spurgeon

| | Posted in Quotes

# Working Backwards in Excel

I recently received a request to help with a salary packaging calculation. I thought I would share the solution and explain the technique to solve it. This is a case where we have a value we need to equal but don’t know the components that make it up. We are in effect working backwards to find the missing value.

“Logic will get you from A to B. Imagination will take you everywhere.”

Albert Einstein

| | Posted in Quotes

# 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.

“We know what we are but know not what we may be.”

William Shakespeare

| | Posted in Quotes

### 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.

| | Posted in Tips

# 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.

A wonderful discussion about creativity and how we can all be creative.

Makes you think.

| | Posted in Videos

“Play is the highest form of research.”

Albert Einstein

| | Posted in Quotes

# Excel and Range References

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?

### 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.

### Related Posts

| | Posted in Tips

# Solving a Date – Time Issue in Excel

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.

# IFERROR Function Now Handles SPILL Errors

Not sure when it happened, but a recent update to Excel has enabled the IFERROR function to handle spill errors.

# Creating A Text String of Lowercase And Uppercase Letters

As a follow on from last week’s post you may want to create a text string of all of the lowercase and uppercase letters. Again we can combine some Excel functions to achieve this.

# Listing Alphabetic Letters in Excel

If you need a listing of the letters of the alphabet you can combine a couple of functions to provide the list.

# Excel Drop Down Improvement

In previous versions of Excel when you had a drop-down list that contained duplicates those duplicates would show up in the drop-down list. This has been fixed in the latest versions of Excel. The duplicates are now removed.

You have to finish things — that’s what you learn from, you learn by finishing things.

Neil Gaiman – author

| | Posted in Quotes

# Updating the Excel Header with a Cell Value

A common request over the years for Microsoft has been to have the ability to link the print header or footer to a cell. As at the time of writing this functionality doesn’t exist, but we can add a one-line macro to fix that.

# SUMIFS Magic Solution

A client recently had a problem. He was chasing a formula that identified if a text string contained one of three words. He wanted to base an allocation on finding those three words. SUMIFS offers a solution.

If you knew how much effort went into it you would not call it genius.

Michaelangelo

| | Posted in Quotes

# Solving a Conditional Summing Text Problem in Excel

I was checking out an old Excel book Excel Outside The Box by long time Excel MVP Bob Umlas and noticed he used the N function in his SUMPRODUCT functions. I then realised why. It converts text to a zero. That gets around an issue with adding up ranges that contains text, thanks again Bob.

“Everything not saved will be lost.”

Nintendo “Quit Screen” message

| | Posted in Quotes

# A New Month of Sundays

One of my more popular posts involved counting the number of Sundays between two dates. With dynamic arrays that becomes easier, and we can create a custom function.

It’s  so hard to forget pain, but it’s even harder to remember sweetness. We have no scar to show for happiness. We learn so little from peace.

Chuck Palahniuk (Novelist)

| | Posted in Quotes

# May the Macros Be With You

May is Macros month.

You can learn how to save time and effort by replacing repetitive or time consuming tasks with a macro. Macros make delegation easier.

Four live Excel webinars to get you started with macros. The first one is free. Over 5 hours of training.

Buy the Essential Macros Bundle for AU\$60 and be registered for all the live May sessions (see below for dates) plus get access to the online courses including bonus macro content.

Essential Macros Bundle 2024

# Can’t push objects off the sheet Error in Excel

I recently had an error pop up when working with a client’s file. The client’s file had a macro that would hide most of the columns on the sheet, but it had started to generate an error message.

Deeds will not be less valiant because they are unpraised.

Source: Aragon in The Return of the King

J.R.R. Tolkein

| | Posted in Quotes

# Dynamic List of Weekday Dates in Excel

Creating a Dynamic list of dates in Excel is pretty easy now with the use of the SEQUENCE function. Creating a dynamic list of weekday dates is a little bit more complex.

# Padding Entries in Excel

Another post inspired by the book 101 Ready-to-Use Excel Formulas by Michael Alexander and Dick Kusleika. This one is Formula #22 and covers padding entries with zeroes.