Clever Clogs tip # 725
6 February 2023


Stop using complicated formulas when there's an easier way (Microsoft Excel) *

Here we are ... tip of the month February.

Some of you know I am active on LinkedIn, where I share my tips and love picking up new ones. But one of the things that does my head in is posts about convoluted formulas in Excel that can be done in a simple way. Especially if the clip involves a kitten sitting on the presenter's shoulder or someone in a TikTok video doing a silly dance. But I digress.

Rather than having a rant on LinkedIn I want you to know about the simple alternatives for the following Functions: SUM, AVERAGE, MAX, MIN, COUNT, COUNTA, COUNTIF, DSUM, DAVERAGE, AND, OR, CONCATENATE, CONCAT, LEFT, RIGHT, MID, UPPER AND LOWER etcetera etcetera. You get my drift.

Stop using SUM, AVERAGE, MAX and MIN
You can use Excel's powerful Table management features. Simply press CTRL + T to create a table. Press CTRL + SHIFT + T to add a row at the bottom and use the various Functions in each column.

Stop using COUNT, COUNTA and COUNTIF
As mentioned above, you can use the Tables' total row to add Functions such as SUM, AVERAGE, MAX, MIN and COUNT in each column. But why not use PivotTables instead?

By the way, no know-how necessary ... click anywhere in your data and select Recommended PivotTables from the Insert tab.

Stop using DSUM, DAVERAGE and DCOUNT
Simply copy/paste the Total row above the table.

Stop using AND and OR
Why not combine Number Filters in Tables instead? The example below shows everyone who is older than (Greater Than) 60 who has been absent for less than 2 days.

Stop using CONCATENATE, CONCAT, MID, UPPER and LOWER
If you are using Excel version 2013 or later you can use the Flash Fill functionality from the Auto Fill Options button. Flash Fill automatically fills your data when it senses a pattern.

You can, for example, use it instead of the CONCATENATE and CONCAT Function to combine first and last names from two different columns.

But you can also use it the other way round ...

Much easier than Data / Text to Columns!

Flash Fill can also be used to convert upper or lower case, instead of using the UPPER and LOWER functions. No need for 'helper columns'!

It can even be used to sense patterns like the one below, removing the first three and last 4 characters.

Apart from the Auto Fill Options button you can also press CTRL + E or use the Flash Fill button on the Data tab.

Hope this was helpful. Let me know what you will stop using!

Recent CleverClogsTipTime on LinkedIn

  1. Stop email addresses being turned into hyperlinks
  2. Schedule messages for later in MS Teams
  3. Eight ways to customise Outlook
  4. One-click Shutdown button
  5. Does anyone use F6?
  6. Use F7 in MS Word
  7. Create AutoText in Word and Outlook using ALT + F3

K.


* Unless stated otherwise this tip is written for Microsoft 365 desktop apps and Windows 10 users, but might also be useful in Office 2010, 2013 and 2016.