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.

pic1

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”

pic2

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.

pic3

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.

pic4

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.

pic5

Advertisements
Posted in Office

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.

pic1

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.

pic2turns into this … pic3

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.

pic4

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.

pic5

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.

pic6

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.

pic7

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.

Posted in Office

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.

pic1

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

pic2

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

pic3

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.

pic4

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.

pic5

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.

pic6

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

pic7

Posted in Office

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.

pic1

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.

pic2

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.

pic3

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.

pic4

Posted in Office

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.

pic1

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.

pic2

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.

pic3

Posted in Office

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?

pic1

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

pic2

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.

pic3

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.

pic4

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.

pic5

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.

pic6

pic7

Posted in Office

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)

Posted in Office | Leave a comment