Archive | Microsoft Excel RSS feed for this section

Tame your computer – find a formula

2 Jul

A function is a special type of formula that “returns” a specific result.  Excel provides more than 400 functions and new ones seem to get added with every release. Functions allow you to perform calculations that would otherwise be difficult, if not impossible, to achieve. Popular functions you might have heard about (or use) are SUM, AVERAGE, IF, AND, OR, LOOKUP, TODAY, NETWORKDAYS, TRIM …

But let’s stick to today’s topic … how to automatically complete formulas.

To make it easier to enter functions and minimise typing errors Microsoft introduced “Formula AutoComplete” in version 2010. If you are not a “hunt-and-peck” typist and look at your screen rather than your keyboard when you type, you will have noticed that as you type the function, suggestions pop up. But did you know there is also a fast way of opening the bracket?

Here’s how:

  1. Type = (equal sign) and the first couple of letters of the function.
  2. Keep typing or use your downward arrow to select the desired formula.
  3. Press TAB.

By the way, if for some reason you cannot bear Formula AutoComplete you can turn it off. Here’s how:

  1. Select Options from the File tab.
  2. Type an F (or click on Formulas if you must).
  3. Untick the Formula AutoComplete check box.
  4. Press ENTER (or click OK).

With thanks to Emma for the tip inspiration!

Related tips:
Find out how many working days are left this year
Countdown to your special day: anniversary, birthday, wedding, retirement or Christmas

Tame your computer – zero in on Excel

31 May

I love Excel but there are a couple of things that really annoy me. One of these things is that you cannot add a zero in front of a number, which I often want to use for telephone numbers and invoices. For example 01223123456 should not be turned into 1223123456. And James Bond’s code number should not be changed to 7!

Oh, and how about large numbers such as credit cards that are turned into some weird code?  For example 1234567898765432 being displayed as 1.23457E+15.

There are various ways you can tackle this, by means of custom formats and the TEXT Function. But if you never need to use the number in a calculation, there is a much easier way.

Here’s how:

  1. Type an apostrophe (‘) in front of the number.

Excel will treat the cell as text (hence the fact that you cannot use it to do a calculation) and the number will be left-aligned. You can obviously reset the alignment by clicking on the Align Text Right button.

Related tip

Tip # 193: Display your numbers with leading zeros

Tame your computer – with a double-click trick

1 May

Back in 2006 (no, this isn’t a typo!) I wrote a tip on how to quickly move to the end of a range of cells in a row or column using keyboard shortcuts. But as I know not everyone is a fan of shortcuts I thought I’d share a “double-click trick” that almost does the same.

Here’s how:

  1. Point to any of the borders of a cell and wait until the mouse pointer appears as a combination of an upward-pointing arrow and four-headed arrow.
  2. Double-click any of the cell’s borders – left, right, top or bottom.

Your cursor will jump to the edge of the range of cells. Simply double-click again to jump in your preferred direction.

Remember, blank rows make it difficult and messy to filter and manipulate data. If you use empty rows for visual purposes, use colours or borders instead.

Related tips:

Quickly move to the end of a range of cells in a row or column
Quickly delete empty rows 

Tame your computer – find the filter

19 Mar

If you can’t find the data you’re looking for in a worksheet, it might be hidden by a filter.  When you apply a filter, entire rows are hidden if values in one or more columns don’t meet the filtering criteria. There are three quick ways to see whether a filter has been applied to the sheet.

Here’s how:

  1. If the button next to one of the column headings displays a funnel it means a filter has been applied.  OR
  2. In the bottom left corner of the workbook, on the Status Bar, the number of records found is displayed or it might simply say “Filter Mode”. OR (my favourite)
  3. The Clear button on the Data tab has a red cross next to it.

To get rid of all of the filters in a worksheet at once click the Clear button on the Data tab. Or better still, right-clickthe Clear button and select Add to Quick Access Toolbar.

In future, if you want to know whether or not a table has been filtered you check whether the Clear button on the Quick Access Toolbar has a red cross and if so, you simply click it to clear all filters.

By the way, if you still cannot find the data, you might have hidden rows or columns. See tip 478 for help on how to find these.

With thanks to Ian for this week’s tip inspiration!

Related tips:
Filter dates by month

Find hidden rows and columns in your worksheet
Filter data in a PivotTable and PivotChart using Slicers
Quickly format and enable filtering of your data
Fast filtering

Tame your computer – top 10 tips for Excel

24 Feb

What I often hear from my course participants is that they came away with new tricks, tips and shortcuts that they never would have found on their own.

Here are your favourite, most commented on Excel shortcuts, with links to the relevant tips. I hope you enjoy them!

1 ALT + ENTER Insert line breaks
2 ALT + 7 Create a bulleted list
3 CTRL + ; Insert today’s date
4 CTRL + A twice Select the entire table, including the table headers
5 CTRL + down arrow Move to end of a range of cells in a column
6 CTRL + G Display the Go To dialog box
7 CTRL + T Format as table, enabling filtering
8 F11 Create a chart in a separate sheet
9 F2 Switch to Edit mode
10 F4 Repeat your last action

By the way, CTRL + G also works in Word and Outlook and F4 in Word and PowerPoint.

Finally, see http://www.roem.co.uk/inc/shortcut_archive.pdf for a list of all the shortcuts published since March 2013 when we launched the ‘shortcut of the week’ on our home page.

Keyboard shortcuts may sometimes be unintuitive or hard to remember, but I drip feed a new shortcut weekly, to help you to boost productivity without reaching for your mouse.

Tame your computer – crafty conversions

9 Feb

In the first tip of this year I wrote how to use the drag and drop technique to move or copy cells. (It’s here if you missed it.)

The disadvantage of this method is that the Paste Options button won’t be displayed, so you won’t be able to perform special copy and paste operations “on the fly”, (such as converting columns to rows and rows to columns). That said, it still offers a great trick if you want to paste values, not formulas.

Here’s how:

  1. Select the cell(s) that contain the formula.
  2. Point to the border of the selection and wait until the mouse pointer appears as a combination upward-pointing arrow and four-headed arrow.
  3. Press and hold down the right mouse buttondrag the cell(s) to the new location and release the mouse button.
  4. Select Copy Here as Values Only.

 

Related tips:

Move or copy cells using drag and drop

Display the Auto Fill Options on the fly

Keep the column width when pasting

How to quickly change columns to rows or rows to columns 

Tame your computer – drag and drop with Excel

6 Jan

Happy New Year! Let’s make 2018 the year that you learn heaps and have fun doing so. Hopefully this tip is a good start and worth checking out …

As you know, there are many ways to move or copy cells in Excel. Perhaps you use CTRL + XCTRL + C and CTRL + V? Or are you a “right-mouse-clicker”? Perhaps you prefer the Cut and Copy buttons on the Home tab?  Or maybe you use AutoFill to copy values and formulas into adjacent cells?

But did you know you can also move or copy any cell into any worksheet location by using a drag and drop technique?

Here’s how:

Moving cells

  1. Select the cells that are to be moved.
  2. Point to the border of the selection and wait until the mouse pointer appears as a combination of an upward-pointing arrow and four-headed arrow.
  3. Press and hold down the mouse button.
  4. Drag the mouse pointer to the new location.
  5. Release the mouse button.

Copying cells

  1. Select the cells that are to be copied.
  2. Point to the border of the selection and wait until the mouse pointer appears as a combination of an upward-pointing arrow and four-headed arrow.
  3. Press and hold down both CTRL and the mouse button.
  4. Drag the mouse pointer to the new location.
  5. Release the mouse button and CTRL.

You can also move or copy between sheets by holding down the ALT key when dragging the content over the sheet tab.

By the way, the Paste Options button will not be displayed when you use the drag and drop method.

Related tips:
Copy the content of a worksheet