Archive | Microsoft Excel RSS feed for this section

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!

Tame your computer – no comment!

29 Nov

Today’s tip is a special one…it’s number 500! I started writing tips as far back as 2003 and at that time I had no idea it would continue for so long or prove so popular. I get regular feedback from readers, who say that they always keep the tips, or even post them on their office noticeboards. Over the years the tips have also been picked up by a number of business publications and websites, such as the Cambridge Network, the Cambridge News and even by BBC Radio Cambridgeshire.  And as I’m still passionate about helping you to make the most of your software I will continue to collect new tips, either through my own training work or through ideas and suggestions from my course participants. So here’s one that was inspired by last week’s group at Acushnet Europe Limited. (Thanks, guys!) How to quickly delete all comments in a workbook.

As you know, creating a workbook is sometimes a team effort, involving several colleagues. One person may be responsible for setting up the basic workbook. He or she may then submit that workbook to another colleague, whose job is to review it and make suggested changes. This is often done by entering a comment, rather than making an actual change. A comment can be a suggestion for a change, a question to the person who created the workbook or any other text that relates to a part of the worksheet. OK, so you might know how to create and review comments. You might even know how to delete them. But what if you want to delete all of them in one go?

Here’s how:

  1. Display one of the worksheets containing the comments.
  2. Click the Select All button. (The empty rectangle in the upper-left corner of a worksheet where the row and column headings meet, i.e. to the left of column A and above row 1.)
  3. If you have more than one worksheet containing Comments and want to remove them all in one go, right-click the sheet you used in step 2 and select Select All Sheets from the drop-down list.
  4. On the Review tab, click on the Delete button in the Comments group.
  5. Make sure you select a single sheet before you continue to work, because anything you do while all sheets are grouped (as indicated in the title bar) will affect all sheets.

Finally, you might want to check out two old tips about comments:

Tip # 392: How to quickly insert a comment in multiple cells

Tip # 142: Print a worksheet with comments showing

Tame your computer – find a formula

1 Nov

Have you ever wondered where the formulas are in your workbook? Perhaps you’ve inherited a file and don’t have a clue what they’ve done. Or you haven’t looked at the data for a while and would like a quick way to spot all the cells that contain a formula.

Here’s how:

  1. Press F5.
  2. Click on the Special button.
  3. Click on the Formulas radio button.
  4. Click OK or press ENTER.

If the workbook does not contain formulas you will see the message “No cells were found.” If it does, all cells will be highlighted, ready to be protected from being modified. But let’s make that the content of a future tip. (With thanks to Erica Adarve for inspiring me to write this tip!)

By the way, you might want to have another look at tip 478 which explained how you can use the Go To Special functionality to find hidden rows and columns in your worksheet.

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

Tame your computer – read between the lines

14 Oct

In Excel, borders can add visual interest to a worksheet, as well as make the data easier to read.  As you know, the lines that you see between the rows and columns don’t appear when you print the worksheet.

You can obviously add borders around the cells, but did you know that there is a very simple and quick way to make the gridlines appear in your printed worksheets?

Here’s how:

  1. Select the sheet(s) you want to print.
  2. On the Page Layout tab, in the Sheet Options group, tick the Print check box.

The gridlines will only be printed if the cell contains data. If you want to print gridlines around empty cells, you must set a print area to include those cells.

  1. Select the cells including the empty cells you want to print the gridlines of.
  2. On the Page Layout tab, click on the Print Area button and select Set Print Area.

By the way, both the Print Gridlines as well as Print Area settings are saved along with your workbook, so you might at some point want to clear the options.

And finally, any borders that you added manually will be printed instead of the gridlines.

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