Your Questions Answered #11 – Tips and Tricks Live extended

• Excel Tips and Tricks Live – Your Questions Answered (June 2024)’ webinar recording now available.

The question

When I send data to my US accountant, he gets very frustrated as the dates get messed up between UK and US formatting. I don't want to change my computer to the US date setting. How do I avoid this from happening? I am currently sending date data as a text field which is not ideal.

Answer

There are two little know tricks that might help you here:

1. The keyboard shortcut Ctrl # converts to dd-mmm-yy e.g. 12-Mar-19 which is the same in US/UK.

2. Or you can select the cells with the wrong dates, click Data then Text to Columns (this isn’t a text to columns issue but the feature is hidden in this menu).

Press next 2 times then choose date and the order from the source. For example, with ‘MDY’ the dates will convert to the style your computer picks up.

You can also see a more detailed walkthrough in this video:

The question

Can you filter in a pivot table but on columns only (i.e. not using the filter function of the pivot table)?

Answer

Yes! If you click in the cell to the top right outside of the Pivot table and add a filter, then filters get added to every cell. This is a hack to remember!

The result:

The question

When using power query to join two data sets, can it be done based on 2 columns criteria without having to do a concatenate first?

Answer

Yes, its quite easy actually! Press Ctrl and click on both columns in the “Merge queries” pop-up. You will then see labels 1 and 2 on the columns which indicate the order in how to merge the queries.

The question

I mainly use Excel for Mac but for some tasks (e.g. some Power Query functionality) I have to use Excel for Windows in emulation on my Mac. What throws me is that many of the keyboard shortcuts for example to navigate to the bottom of a range, do completely different things on the Windows version.

Answer

Microsoft did their best to align the keyboard shortcuts across the platforms a couple of years ago, but there are still some differences. However, you can manually set custom keyboard shortcuts in Mac unlike in Windows which you may be able to use to align the shortcuts you use in Windows to Excel in Mac.

Last month, Microsoft launched the accelerator keyboard shortcuts called KeyTips with Mac using Alt or Option key which had been available for a while on Windows.

The question

How do you stop the banner saying " "Slow Workbook? Excess formatting and unneeded metadata cause large, slow workbooks. Check for improvements to performance" appearing each time you open a file?

Answer

This is Excel telling you that you can clean up your file, it can be super helpful!

If you click the “check performance” button which Excel prompts you with, then “Optimize all”, Excel will reduce your file size without harming any useful data. In seconds you have reduced your file size and the warning will not come up again.

You can learn more about this in this video below:

The question

Can you show us some practical use cases for Copilot in Excel?

Answer

Copilot in Excel can be used for a range of things in Excel from automatically highlighting text to creating complex formulas. However, there are some limitations. Currently this only works on data in a Table. This tool will get better over time but for now this has been covered in this article on Microsoft’s Copilot.