Archive | Microsoft Excel RSS feed for this section

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

Tame your computer – rapid repetition

19 Nov

Many, many moons ago (24 March 2006 to be precise) I wrote a tip on how to print a specific row or column on every page of a printed worksheet. This is particularly helpful when your worksheet is too big to fit on one page. After all, you can have 1,048,576 rows and 16,384 columns!

The instructions were written for Excel 2000, but I bet you’ve upgraded since then and the steps have changed a bit. As I still meet delegates who get very excited when they no longer have to use sticky tape or can stop copying and pasting the data, I thought it was time to write about how to specify rows and columns to repeat on each printed page using Excel 2007 onwards.

Here’s how:

  1. Click on the Print Titles button in the Page Setup group on the Page Layout tab.
  2. Click in the Rows to repeat at top box.
  3. With the Page Setup dialog box open, click on any of the cells in the row(s) you want to repeat on each page.
  4. Click in the Columns to repeat at left box.
  5. With the Page Setup dialog box open, click on any of the cells in the column(s) you want to repeat on each page.
  6. Click OK or press ENTER.

By the way, you still cannot do this if you accessed the Page Setup dialog box from Print Preview.  I would have thought they’d have fixed that by now…

Related tips:
Print the lines between rows and columns to make reading easier
Change the starting page number in the header or footer
Fit a worksheet on the printed page
Print a specific row or column as your title on every page of a printed worksheet

Tame your computer – shortcuts to selection

5 Nov

Since Excel 2007 you can quickly turn a range of cells into something resembling the stock listing paper that mainframe computers used to print out. (Remember those faint green horizontal lines?) It will also switch on the filtering functionality, making it easy to summarise and analyse your data.

It used to be called a list, but it is nowadays known as a table. Hence the fact that both CTRL + L and CTRL + T work to quickly create a table. (See Tip #429)

An Excel table can be thought of as a database. With it, you can store large (or small!) amounts of information and organise it any way you wish. You can also use its powerful Table Tools to quickly retrieve data and manipulate it in numerous ways.

A table typically consists of rows and columns, which you can obviously select just like you would select them in a worksheet. But if you want to select the data only – for example, in order to copy and paste it – you can use some simple keyboard shortcuts.

Here’s how:

  1. Select the entire table, including the table headers: press CTRL + A twice.
  2. Select the data in a row, not the entire row: click the first cell in the table row and press CTRL + SHIFT + RIGHT ARROW.
  3. Select the data in a column (not the entire column): click the first cell in the table column and press CTRL + SHIFT + DOWN ARROW. (Or click anywhere in the table column and press CTRL + SPACEBAR.)

Related tips:

Quickly format and enable filtering of your data

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

Tame your computer – build a bullet list

23 Oct

Microsoft Excel does not have a built-in function to create a bulleted list like in Word, PowerPoint and Outlook. But during a course yesterday I found out you can create one using a nifty keyboard shortcut.

Here’s how:

  • Select the cell where the list is to appear.
  • Press ALT + 7 on the numeric keypad.
  • Type your text.
  • Press ALT + ENTER if you want to insert another bullet in the same cell.
  • Repeat steps 2 – 4.

With thanks to Alex for the tip inspiration!

Related tips:

Tip # 506: Fast way to start a bulleted or numbered list (Microsoft Word and Outlook)
Tip # 458: How to add, find and remove line breaks (Microsoft Excel)

Tame your computer – fail-safe your formulas

6 Aug

By default all cells in a worksheet can be modified by anyone who has access to that file.

If you want to prevent other users from changing, moving, or deleting formulas you can make those cells read-only. Other data in the sheet can still be changed.

Here’s how:

  1. Select all cells in the worksheet by clicking on the top left corner of the sheet. (Or click in any empty area and press CTRL + A.)
  2. Right-click anywhere in the sheet and click on Format Cells (or use CTRL+1).
  3. On the Protection tab untick Locked and Hidden.
  4. Click OK or press ENTER.
  5. On the Home tab, in the Editing group, click on the Find & Select arrow and click on Formulas.
  6. Right-click any of the selected cells and click on Format Cells (or use CTRL+1).
  7. On the Protection tab tick Locked and Hidden.
  8. Click OK or press ENTER.
  9.  On the Review tab, click Protect Sheet.
  10. Enter a password in the Password to unprotect sheet box and click OK. Re-enter the password in the Confirm Password dialog box.
  11. Click OK or press ENTER.

Step 10 is optional, but if you do not set a password, anybody can remove the protection by simply clicking on the Unprotect Sheet button on the Review tab.  The warning message that pops up even tells you the way to unprotect the sheet.

* Unless stated otherwise, these tips were written for Microsoft Office 2010.

Tame your computer – deal with dates

14 Mar

When you enter a date such as 4/3 in Excel,  the default date format is 04-Mar. You can quickly reformat it using the drop-down button in the Number group on the Home tab and select Short Date or Long Date, but what if you also want to display the day? For example, Tuesday 14 March 2017? Some people use the Text Function to in a separate column (=TEXT(A1,”ddd”) but there is a way to format dates to include the day of the week.

Here’s how:

  1. Right-click the cell containing the date(s) or the whole column.
  2. Select Format Cells from the menu.
  3. If necessary (probably not) display the Number tab.
  4. Select the Custom option in the Category box.
  5. In the Type box, double-click on the word General and type the custom format of your choice. (Mine is ddd dd mmmm yyyy.)
  6. Press ENTER or click OK

Herewith some other options you might like to try out in step 5  … to get Tuesday 12 December 2017 when you type 12/12, set the Custom format as dddd dd mmmm yyyy. Or if you prefer to see it as Tue 12/12/17 try out ddd dd/mm/yy. The underlying date you typed won’t change and can be checked by looking in the Formula Bar.

Related tips:
Display your numbers with leading zeros : http://roem.co.uk/tip_193.html
Convince Excel you want to type July 2010 : http://roem.co.uk/tip_313.php
Save time entering dates : http://roem.co.uk/tip_442.php