One Minute to Excel #16 – Create a useful macro

Wow - a replacement for Merge cells in less than a minute

The Merge cells format has lots of issues. It can crash macros and stop you copying and pasting.

In less than a minute you can use a macro to solve the problem.

Center Across Selection takes a few mouse click to apply but it doesn’t have the issues Merge cells does.

A macro can do it much quicker – see how in this quick video.

One Minute to Excel #15 – Replace Colours

It can be done

Replacing colours manually can be a tedious task.

Did you know you can use Excel’s built-in Find & Replace to do the job for you?

See how in this short video.

One Minute to Excel #14 – Flash Fill

Excel's Super Hero

Joining names; extracting codes or converting dates is usually done with formulas, but there is now a formula-free solution called Flash Fill.

See how you can do four transformations in less than a minute.

Name box shortcut

Just found out you can double click the re-size control on the Name Box. This quickly shrinks the Name Box width.

I have no idea how long that feature has been there, but I have just found it. Woohoo!

Related Posts

 

One Minute to Excel #13 – Create and Use a Check Box

Tick it to the next level

A check box is an easy interface to create and use.

See how to add one to a sheet and use it in a calculation.

One Minute to Excel #12 – Hide Cell Entries

It is a special format

You can’t hide a cell, but you can stop the cell value from displaying on the sheet.

It involves a custom number format.

Open the Power Query Editor

If you have cell selected in the output table from a Power Query, you can press, in sequence (not held down) the following keys Alt P U E to open the Power Query Editor window.

Related Posts

 

One Minute to Excel #11 – Add % to a Pivot Table

It is not intuitive

Often people perform calculations off to the right of Pivot Tables to calculate percentages.

In this short video I show you those calculations can be done inside the Pivot Table itself.

The solution is not intuitive, but it is easy.

This example builds upon the previous One Minute to Excel post.

Data Validation Shortcut

I typically use the shortcut Alt A V V pressed in sequence (not held down) to open the Data Validation dialog.

I like it because you can do it one-handed. The A and V are close together.

There is another shortcut that works the same. Again, pressed in sequence and not held down. Alt D L

Use the one that is easiest for you.

Related Posts

Pivot Table Shortcuts

Here’s a couple of useful keyboard shortcuts for Pivot Tables.

Display/Hide the Pivot Table Field List – this list lets you create or change the Pivot Table.

Alt J T L  – pressed in sequence, not held down.

 

To add Subtotals above the entries in an existing Pivot Table.

Alt J Y T T – again pressed in sequence, not held down.

Related Posts

One Minute to Excel #10 – Create a Pivot Table

It is easy

Note sure why, but Pivot Tables are often seen a “hard” or “advanced”.

In the short video we see how easy they are.

Oops – I go over my one minute time limit by a few seconds because I format the Pivot Table as well.

Expand the Formula Bar

The Formula Bar can be expanded using the icon on the end. But there is a keyboard shortcut as well.

You can expand it or return it to one line using the keyboard shortcut Ctrl + Shift + U.

Thanks to Excel MVP Tom Urtis for sharing this shortcut recently on LinkedIn.

Related Posts

One Minute to Excel #9 – Drop down list in a cell

Three techniques

This short video covers different ways to insert a drop down list into a cell.

I go over my one minute time limit by a couple of seconds, but I do cover three techniques.

One Minute to Excel #4 – Copy Anything

Mouse and keyboard combined

Copying is a common task in Excel. This technique applies to most things in Excel form cells and range to charts, images and sheets.

It also works in Word and PowerPoint.

Have you used the mouse and keyboard together? It is time to start.

Let’s go.

New Task Pane Icons

Seems new icons have been added in the right of screen to allow you to switch easily between Task Panes in a recent upgrade.

This is in the subscription version of Excel.

Power Query shortcut for Adults

The shortcut to display the Queries & Connections Task Pane is easy to remember. It is Adults Only!

To display or hide the Queries & Connections Task Pane you use. These keys are pressed in sequence, not held down.

So only adults allowed in Power Query.

Related Posts

 

Windows Clipboard

Did you know you can accumulate copied entries in the Windows clipboard?

The default setting is to only have the last thing you copied, but a setting change can give you access to multiple items to paste.

You can also pin items to the clipboard to keep them there.

To change the setting.

Click the Windows button and choose the Settings icon

The select System.

Then select Clipboard (bottom left) and change the Clipboard History to On – done.

 

If you copy multiple items you can press the Windows key and V to access what’s on the clipboard.

Then you can click the top right elipse icon to Pin the item to the clipboard.

Related Posts