excelshed

More about multiple values in a cell

Wed, 28 Oct 2026 23:03:13 GMT

In last week's blog, we looked at an example of filtering a column, where we had multiple values within each cell.

This week, I am following up with an example of using a function to create a list within a cell.

On the left, in the screenshot below, we have a list of split times from a 10km run.

On the right, in the blue cell, is a formula that shows the traditional use of the LARGE function to create a horizontal spilled array with the 3 best lap times.

On the right, in the green cell, a modified version of the LARGE function (just wrapped in curly brackets), that creates the 3 best lap times within a single cell.

What? Multiple values in a cell?

Fri, 23 Oct 2026 03:46:44 GMT

Excel will soon introduce support for multiple values within a cell. This will make a few tasks easier to achieve than they were before.

Here's a simple example:

Previously when you had several items within a cell, you would have to first create new columns for each item, if you wanted to filter them.

But now, you just select the column (like the Hobbies column below) and make it in to a 'List' (Insert tab, Controls group). Then you can filter away!

This feature is now in the 'Insider' version and will roll out to other versions over time.

Forgot to name your Table?

Fri, 16 Oct 2026 03:14:51 GMT

NEW! Naming your Table straight away

I'm sure you've done this before: create a Table, go to refer to it in a function but only to realise that it's called Table1, Table2, etc.

Then you have to decide whether to abandon your function writing and go and name your Table, or be lazy and leave it be.

GOOD NEWS! Excel have just added the option of naming your Table when your first create it!

If this new feature isn't yet in your version of Excel 365, then it will be soon :)

New Paragraph

Stop using Enter!

Fri, 09 Oct 2026 01:56:22 GMT

The annoying thing about pressing Enter is that you end up at the next cell down; you then have to click back on the cell above to do what's next, e.g. making it bold, colouring it or filling across.

So about using Control + Enter instead? This has the same effect as Enter, but you remain on the same cell.

I use Control + Enter more than I do just Enter.

Stop using Control SHIFT down arrow!

Thu, 01 Oct 2026 10:33:56 GMT

Stop using Control SHIFT down arrow

If you use Control SHIFT down arrow to select from the current cell down to the last cell in a column, then you are unlikely to ever forget it. It remains a favourite keyboard shortcut for many users.

BUT there is an alternative shortcut which is relatively new and much easier to remember - just SHIFT double-click on the cell's bottom border to get the same result!

And yes it also works upwards, to the left, and to the right.

Stop using Merge & Centre!

Thu, 24 Sep 2026 03:58:14 GMT

Stop using Merge & Centre!

If you use Merge & Centre, chances are that you have received messages along the lines of 'Sorry, you can't sort/filter this data as some cells are merged'. Excel purists don't like Merge & Centre because of this and other drawbacks (such as breaking macros).

There's always been an alternative! The Centre Across Selection feature was a better solution but the button was well hidden (in the Alignment tab of the Format Cells dialogue box, accessed from the Font group on the Home tab).

The good news is that Microsoft have now made this feature directly accessible from the Merge and Centre button itself.

So, stop using Merge & Center; start using Centre Across Selection instead (it should soon appear in your copy of Excel 365).

The Amazing Story of Excel's Phantom Day

Fri, 18 Feb 2022 07:18:17 GMT

Why did Microsoft include a day in Excel that never actually happened?
Plus, find out how toilet paper has a 'roll' to play.

This is the amazing story of Excel's phantom day. Right from the very first version (and still remaining today), Excel has included 29 February 1900, a day that never existed...

excelshed

More about multiple values in a cell

What? Multiple values in a cell?

Forgot to name your Table?

NEW! Naming your Table straight away

Stop using Enter!

Stop using Control SHIFT down arrow!

Stop using Control SHIFT down arrow

Stop using Merge & Centre!

Stop using Merge & Centre!

The Amazing Story of Excel's Phantom Day

Why did Microsoft include a day in Excel that never actually happened? Plus, find out how toilet paper has a 'roll' to play.

Why did Microsoft include a day in Excel that never actually happened?
Plus, find out how toilet paper has a 'roll' to play.