Excel defaults

Below are some Excel defaults that can make using Excel easier. The instructions are for Excel 2010, but these options are also available on older versions, too.

Number of worksheets

By default, a new Excel workbook will include three worksheets. And normally we use one! You can change the default to any number of worksheets, including one, which will make for smaller files and less confusion as we look at empty worksheets.

1. On the menu, select File then Options
2. Under General, change the number under “Include this many sheets:”

Once you change this, any new workbooks will default to that number of worksheets.

BONUS: While on this screen, you can also change the font type and size.

Change how the cursor behaves when you hit [Enter]

Normally, if you hit [Enter], the cursor moves down to the next cell. If you need it to move in a different direction, make this change.

1. On the menu, select File then Options
2. Under Advanced, change the setting for “After pressing Enter, move selection”

BONUS: While on this screen or any other Excel Options screen, notice that some groups have a drop down list. This means some settings are for all of Excel and any workbook while some settings, the ones with the drop down lists, only apply to a single workbook.

Default file format

Excel 2010 will default to a file format known as “Excel Workbook”. This is the .xlsx extension and is a standard Excel format. You can select other format, like the prior version called “Excel 97-2003 Workbook” and new files will always open in this file format.

Extra tip for those that read the entire post!

Rows are labeled with numbers and columns are labeled with letters. But what if you want both labeled with numbers. Or more likely, both are labeled with numbers and you want to set it back! Go to File, Options and select Formulas. Under Working with formulas, the R1C1 reference style should be unchecked. If you check it, the columns will also be numeric.

Two Word tips, sorting and format painting

Here are two Word tips to save some time, using Word 2010.

Sort a bulleted or numbered list

If you have a list already in Word, you can quickly put it in alphabetical order with a few mouse clicks. First, select the text in your list. On the Home tab, under the Paragraph group, click the sorting button.

A “Sort Text” dialog box will appear. By default it will sort by Paragraphs and Text. Leave those settings and click OK. Your list will now be sorted.

turns into this … 

Format Painter

The format painter is available in several office apps and allows you to format text and then copy all of that formatting to other text. You don’t have to remember all the steps to format the new text. Below we have two paragraphs of boring text using the Calibri font, 11 pt.

I have taken the time to do some fancy formatting on the first paragraph including several font changes and color changes because I decided that my Word doc needed to look like a Latin ransom note.

If I don’t remember all the steps I took to get my fancy look, I just need to use the format painter. Select the text that you want to copy. You don’t need to select the entire paragraph, just some text that has all the formatting you want to copy. And remember, we are only copying the formatting, not the text itself. On the Home tab, in the Clipboard group, click on the Format Painter button. Your cursor should turn into a small paintbrush.

Now select all the text that you want to change. Once selected, release the mouse button and the font will change to the copied formatting.

This works perfectly if you want to copy the formatting once, but what if you have several areas where you want to copy the same formatting? Instead of clicking the Format Painter button, double click the button. Now you can apply the formatting on as many areas as you like. To turn off this feature, hit [Esc] or click the Format Painter button one more time.

Removing the background from a picture in Office

Today’s tip is an advanced one so sit back and get ready for a lot of screen shots!  We will use Word 2010 for the example of removing the background from a picture. This also works in other Office apps.

The first step is to have your Word document open and select the picture with your left mouse button.

With the picture highlighted, the Picture Tools tab should be visible and in the Adjust group is the “Remove Background” button.

Word tries to select what is the background, but as you can see, it may not have done a great job.

First step is to take the square highlight and resize it so that it includes what you want to keep, but excludes as much as possible, like below.

Now it is almost perfect, but see the arrow above? There is a background spot that is peaking through.

If you look at the ribbon, it has changed to a special Background Removal tab. There are two buttons that allow you to mark spots on the image to change. One marks areas to keep and one marks areas to remove.

I clicked the “Mark Areas to Remove and clicked on the little background spot and it went away! Notice the small minus symbol on the picture. If I changed my mind or it wasn’t right, I could delete the mark.

Once you are happy, click the “Keep Changes” button and be amazed with your new image!

Word 2010 header and footer basics

If you want to have text appear at the top or bottom of every page of a Word document, do you start a-typing on each page? Headers and footers are the way to go. They allow you to enter text once and have it appear on every page.

To use a header or a footer, go the Insert tab and the Header & Footer group.

You have the option to use a header or a footer and also a special selection called Page Number. Because page numbers are so common, this special button gives you multiple options for including page numbers in the header or the footer of a document…even in the left and right margins! But back to our headers and footers, you can see that you have quite a few built-in selections to choose from when you press the button.

Once you have a header or a footer in a document, you just need to double click on that area to make changes. You should also see that a special tab appears. Notice below, there is a flag that shows I am in the header section and the special design tab is showing.

You have all kinds of options from where the header is located to using a different header on the first page to adding dates and times automatically.

To close the special tab and get out of the header or footer, you can either double-click on the body of the document or use the “X” button at the end of the special tab. Hitting the [Esc] key also works.

And if you don’t want all the fancy helpers? You just want to insert a header or footer, type and be done with it? Use your left mouse button and double click in the header or footer area (top or bottom of the page). This will open the section with the special design tab, but you can start typing without having to choose one of the built-in templates.

If you need more help, hold your mouse pointer over the Header or Footer button. A pop-up window appears and then hit F1 for the built-in Microsoft help.

Navigating Excel Worksheets

In case you have forgotten a couple Excel terms…the file is called a workbook and each sheet is called a worksheet.

Once you start storing a lot of data in Excel, you may have workbooks with many worksheets like the example below.

Navigating all of these worksheets isn’t always fun using the four navigation buttons. These help you go to the beginning, the end, left one sheet or right one sheet.

You can also cycle through them using your keyboard. [Ctrl]+[PgUp] and [Ctrl]+[PgDn] will move left and right through all of your worksheets.

But did you know that you can go directly to a worksheet without scrolling? Right click on any one of the four navigation buttons and you will get a pop-up menu that lists all of your work sheets. Select the worksheet you want to use and left click.

Excel 2010 Comments

Today we are going to take a brief look at using comments on Excel worksheets. Comments were available in earlier versions of Excel, but the screen shots are from the 2010 version.

We’ve all sent a spreadsheet (or two or three!) and typed that lengthy email explaining the details. Or wasted a paragraph explaining what a cell or formula means. Maybe you have a workbook that only you use, and you have one worksheet with text explaining how a cell works or what to enter. You may even be working with other people and want to make your comments visible to the group. Excel comments are a great tool for adding notes to a worksheet.

In the sample below, we list hours worked and use a formula to calculate how many hours were overtime. We want to let users know that they just have to change the weekly hours cell if the standard hours change, no need to change each formula. We also give a “heads-up” comment so that the user knows that if there was no overtime, the cell will be blank.

Here is the worksheet with one of the formula cells selected. We can determine from the formula how it works, but wouldn’t it be nice to add a comment for everyone?

Here is the same worksheet with two comments, one explaining how to change the standard hours and one explaining the formula.

To work with comments, go to the Review tab on your ribbon. There are buttons to add and delete comments, cycle through each comment and even choose how they display on your screen. The Comments group includes the ability to show all comments or hide individual comments. “Show All Comments” is highlighted below and that is why we saw all the comments in the above screenshot.

To add a new comment, click on the cell that needs a comment. Once you enter a comment, that cell will have a small red triangle in the top right corner to indicate there is a comment available. You will see that mark even if you hide all comments. If a comment is hidden, you can move your mouse over it to see the comment temporarily. The Previous and Next functions also work even if comments are hidden.

Once a comment is visible, you can resize it with the “handles” or even move it. See the comment above that is farther away from the cell.

To edit a comment, just click on it and start typing. Another way to edit, delete or show/hide an individual comment is to right click on the marked cell.

To include comments when you print, head over to the Page Layout tab and the Page Setup group. Click on the small expand button. Then choose the “Sheet” tab. You have three options for how they print. Only visible comments will be included on your printout. This setting will be saved when you save the workbook.

Some standard Windows keyboard shortcuts

These have been around for a long time, but in case they have been forgotten, here are some standard keyboard shortcuts that work in Windows and many Windows applications. You will also find that some of these can be found on your keyboard, on the front of the keys.

[Ctrl+B] Make selected text bold or start typing in bold

[Ctrl+I]  Make selected text italicized or start typing in italics

[Ctrl+U] Make selected text underlined or start typing with underlines

[Windows+E] Windows Explorer opens

[Ctrl+A] Select all, great when in Windows Explorer and selecting files. Also can select all text in a document.

[Ctrl+X] After selecting text or file(s), this will “cut” text or file(s)

This means the text or file(s) will be moved to the new location and deleted from the original location

[Ctrl+C] After selecting text or file(s), this will “copy” text or file(s)

This means the text or file(s) will be copied to the new location and saved in the original location

[Ctrl+V] After selecting text or file(s), this will “paste” text or file(s)

Outlook 2010 Quick Steps

Outlook 2010 has a new feature called Quick Steps. A quick step is a macro that allows you to automate steps in Outlook. Quick Steps can be found on the Home tab.

An good example is one that is built-in called Reply & Delete. If you hit this, you can type and send a replay and Outlook will automatically delete the original message. You will just have your sent message. Use the More button (that little button in the bottom right of many groups on the ribbon). This will open the Manage Quick Steps dialog where you can see existing Quick Steps, create new ones or delete ones that are no longer needed.

To create a new Quick Step, you can base it on one of the defaults or choose Custom.

The Edit dialog will appear. Give your Quick Step a good, descriptive name. On this screen, you can choose each action that you want to automate. You can even create a keyboard shortcut for your new macro. Once you have created a new Quick Step, it will appear in the Quick Steps group.

In the example below, I want a single click to take the email I have selected and delete it, while creating a new task with the email text. Useful to control your email, while not losing track of tasks that you need to complete.

And this is just the beginning! You are not limited to two actions, keep going! Think about what you do repeatedly in Outlook and try to automate it.

Some basic Excel tips

Here are some basic tips to make your spreadsheets look and work better.

• Resize your rows and columns

If your data is wider than the default cell width, resize them so that all the data shows. Before shot is on the left and see how much better it looks on the right?

     

To resize all the columns at once, click the cell above row 1 and to the left of column A. The entire worksheet will be selected. Now move your cursor in between any two columns until it changes to the resize arrows and double click. All columns will be resized to fit the widest entry.

 

• Let Excel do calculations

If you have figures you need to add, values to calculate, always let Excel do it for you. Not only is it more accurate, but if values change, your calculations will change, too.

• 3D isn’t always best

Try to stick with 2D charts as they are easier to read and manipulate.

• Name your worksheets

Notice how the second screen shot actually tells you what is on each worksheet. This is much easier to work with than “Sheet1”. To rename, either double click or right click to bring up the menu. You can also set different colors here.

     

• Merged cells versus Center Across Selection

Look at the two rows below, can you see the difference? Both have words centered over several columns (A-D in this case). And both were done by formatting the cells using the Alignment tab. Row 1 was done with the Merge Cells checkbox. Notice when you click on cell A1, you actually select A1:D1.

But when you click on cell A2, you only select that cell. That is because Row 2 was done using the Center Across Selection option under horizontal text alignment.

 

 

 

And the big question is who cares? You might! You cannot insert a cell if any of the cells in that column are merged. You also cannot create an Excel Table if there are merged cells in your range (we’ll discuss Excel Tables soon!). You cannot sort or filter a range that has merged cells. You may not run across these issues often, but it’s good to know the difference.

Bonus tip for those who read the entire post!

Format Painter

If you spend any time formatting a cell using things like bold, italics, cell borders, you know it can make your spreadsheet really stand out. But if you have to apply several formatting steps to any other cells, you don’t have to go through all the steps again. Using your cursor, select the cell that has the formatting you want to copy. Then click on the Format Painter icon on the Home tab under the Clipboard group.

Your cursor will change to a large plus with a paint brush. Click on another cell and the formatting will be copied to the new cell! You can also click and drag to copy the formatting to more than one cell.

Copying conditional formatting in Excel

Conditional formatting is a wonderful feature in Excel. It allows you to format cells based on conditions such as making a cell red if they are negative. Check the web link below for the tip on conditional formatting.

Today’s tip is about how to copy existing conditional formatting. The example below shows that some cells in column B that are formatted to have a red font if they are less than zero.

If we add more columns, Excel 2010 does something pretty slick. It assumes you want to continue the conditional formatting and does it for you automatically.

If your version of Excel doesn’t do this or if you applied the formatting to some cells and want to expand it to a new range, you have a few options.

• You can copy and paste the formatting. Copy a cell which has the conditional formatting you want and select the new cells. Choose Paste Special and then Formats.
• Select the cell that has the conditional formatting and click the Format Painter. Click on the cell or cells and the conditional formatting will now match.

• The third way is different for older versions versus Excel with the Ribbon. See the sample below, where the conditional formatting is only applied to B3:B5.

1. For pre-Ribbon versions: To apply it to the entire column (B3:B9), just select the cells you want formatted, making sure that at least one cell in the range already has the conditional formatting you want to copy. Choose conditional formatting and the formula should already be populated. Click ok and you are done!
2. For Excel 2007 and 2010, highlight a cell in the range that has the conditional formatting you want to copy. Choose conditional formatting and Manage Rules. The existing rule should be there, you just need to change the range by clicking the button and selecting the new range.