Archive | Microsoft Excel RSS feed for this section

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

Advertisements

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

Tame your computer – fancy filtering

21 Jan

Do you use dates in Excel? If so, you have probably at some point needed to display only those containing relevant information.

For example, you might want to filter out all deliveries you are expecting in February. Or all courses planned for March. You can obviously use Excel’s Find functionality (CTRL + F) to find a specific date. Or perhaps you use the Date Filters and select, say, Next Month. But did you know you can use the table’s Search functionality?

Here’s how:

1.       If necessary, create a table. (CTRL + T or CTRL + L, anyone?)

2.       Click on the drop-down arrow of the date’s column header.

3.       Click in the Search box and type (part of) your search term. For example, Feb.

4.       Press ENTER.

And no, this isn’t relying on you having entered your dates as, say, 16 February 2017. As long as the entries have been formatted as dates, this should work. And remember, there is no need to type the current year if the date you want to enter is part of this year. Simply type, say 16/2.  Perhaps have another look at tip 442.

Related tips:
Tip 358: Enter the current date and/or time into a worksheet  (http://roem.co.uk/tip_358.php)
Tip 426: Fast filtering (http://www.roem.co.uk/tip_426.php)
Tip 429: Quickly format and enable filtering of your data (http://roem.co.uk/tip_429.php)
Tip 442: Save time entering dates (http://roem.co.uk/tip_442.php)
Tip 474: Filter data in a PivotTable and PivotChart using Slicers (http://roem.co.uk/tip_474.php)

Tame your computer – the top 10 tips of 2016

19 Dec

This will be the last tip of the year as I’m pretty sure that you’re all ready to tuck into mince pies and mulled wine.

So here are your favourite, most commented on, tips from the last 12 months. Don’t forget to check out number 6 before you’re off on your Christmas break!

  1. Add holidays to your calendar (Microsoft Outlook) – http://roem.co.uk/tip_471.php
  2. 10 tips for safe online banking – http://roem.co.uk/tip_473.php
  3. Make text look like it was marked with a highlighter pen (Word and Outlook) – http://roem.co.uk/tip_475.php
  4. Stop AutoCorrect from capitalizing text following specific abbreviations (Microsoft Office) – http://roem.co.uk/tip_476.php
  5. Create, store and insert frequently used text and graphics (Microsoft Word and Outlook) – http://roem.co.uk/tip_486.php
  6. Five tips to prevent email overload upon your return from holiday (Microsoft Outlook) – http://roem.co.uk/tip_489.php
  7. How to remove limitations of what is displayed in a cell (Microsoft Excel) – http://roem.co.uk/tip_491.php
  8. Set the default colour of a hyperlink (Microsoft Outlook) – http://roem.co.uk/tip_492.php
  9. Automatically move low-priority emails from your inbox (Microsoft Outlook) – http://roem.co.uk/tip_496.php
  10. Change the emphasis of a SmartArt graphic by changing its direction (Microsoft Office) – http://roem.co.uk/tip_501.php

Wishing you a relaxing holiday season. Escape isn’t just a button on your keyboard!