Categories
Excel 2007

Create a Bulleted List in a Single Cell or Text Box in Excel

As you know, you can enter multiple lines within a single cell by simply clicking Alt + Enter, which starts a new line for you.

Follow the steps below to learn how to create your bulleted list:

  1. Click in the cell or text box where you would like to add bullets.
  2. Holding down your Alt key, key in 0149, which will create a bullet for you.
  3. Key in your first line of text.
  4. Hold down your Alt key and click Enter to create a new line.

Just repeat the steps above for each bullet point you would like to add to your list.

Categories
Excel 2007

Quickly enter Data in your 2007 Excel Worksheet

A really cool shortcut you can use in Excel 2007 allows you to copy data from the cell above the current cell.

Follow the steps below to learn how:

  • Click Ctrl + “ (quotation mark)

Say you enter the number 456 in cell A6. If you then move to cell A7 and click CTRL + “, 456 appears in the cell.

If you then go to cell A8 and key in CAB and click CTRL + “ you will see CAB456 in your cell.

This can really come in handy when you are entering data with a lot of similarities.

Go ahead and give it a go and see how much time you can save!

Categories
Excel 2007

Highlight Matching Values in Excel 2007 Quickly

There are many ways of finding matching data in your Excel worksheets, the most versatile of which is filters. Most likely the easiest way is sorting. But if all you really want to do is see the matching values, there is a much quicker way to accomplish this and as you all know, I love to save time and keystrokes!

Follow the steps below to learn how:

  1. Select/highlight any cell in a column in your worksheet that contains the value you would like to find the matches for.
  2. Click CTRL + Space to select the column.
  3. Click CTRL + Shift + \.

The key combination in the last step above selects all the cells in the column that do NOT match the value in the active cell.

Pretty cool eh?

Categories
Excel 2007

How to Create a Pivot Table in Excel 2007

I had an inquiry last week asking me how to create a pivot table in Excel 2007. Pivot tables are a great way to summarize a large amount of data to divulge a new meaning from it. The name is derived from being able to pivot the data in PivotTable view.

Follow the steps to learn how to create your pivot table in version 2007:

  1. Click your Insert tab.
  2. Click the PivotTable button.
  3. Excel will automatically select the entire range, but you can modify it, if necessary.
  4. Decide where you want to place your PivotTable (New Worksheet is the most common area).
  5. Click OK.
  6. Select a field.

Your PivotTable appears.

To populate your PivotTable with data fields from the list on the right:

Drag it onto the PivotTable grid, to one of the defined areas.

Repeat the process to populate the other placeholders with data fields.

Voila! You now have a PivotTable!

  • To filter by a field, simply open its drop-down list.
  • Select the value by which to filter.
  • Click OK.
  • To move a field to a different placeholder, drag it to a different section in your field list.
  • Your table will now display data only for the filtered criteria.
  • You may need to resize the pane to see the field in its new location.
  • To reorder fields within a placeholder, drag them up or down.
  • To sort by a field, open it’s drop-down list.
  • Select one of the sort orders.
  • To create a chart out of the data, click the PivotTable button.
  • Click OK.
  • Select a chart type.

To see the chart better, close the task pane.

Categories
Excel 2007

How Do I Add a Column in Excel 2007?

I was recently asked how to add a column in Excel version 2007. I don’t know too many folks who are thrilled with the Ribbon interface, but once they learn how to navigate it seems to grow on them.

Inserting a column or row into an Excel worksheet is easy.

  1. Simply select a column or row and chose the appropriate command from the Insert ,enu.
  2. In Excel 2007 click the Home tab and select an item from the Insert drop-down int he Cells group.
  3. If you are like me, you will prefer the shortcut version which is right-click a selected column or row and select Insert from the resulting menu.

Excel will insert a column, pushing everything to the right by one column.

When inserting a new row, Excel will shift everything down by one row.

There are some other shortcuts you can use without using your mouse:

Insert Column CTRL + space, CTRL + Plus sign
Delete Column CTRL + Space, CTRL + –
Insert Row Shift + Spcae, CTRL + Plus sign
Delete Row Shift + Space, CTRL + –

It’s all in knowing where to look isn’t it?

Categories
Excel 2007

How to Add Pop Up Documentation in Excel 2007

Did you ever wish you could make your worksheets more helpful to the folks who have to use them? Well you can! You can add a pop-up window that will display documentation whenever the cell is selected.

Follow the steps below to learn how:

  1. Select the cell in which you would like your pop-up to appear.
  2. Display the Data tab of your Ribbon.
  3. In the Data Tools group, click the Data Validation tool.
  4. Be certain the Input Message tab is displayed.
  5. Be sure the the Show Input Message When Cell is Selected check box is selected.
  6. In the Title box, key in a title for your pop-up window.

This title will appear in bold at the top of your pop-up window.

  1. In the Input Message box, key in the text of the documentation you would like to appear in your pop-up window.
  2. Click OK.

If you have followed the steps above, when data is accepted into that cell in your worksheet, MS Excel will display a helpful message.

Categories
Excel 2007

How to Set Default Column Width in Excel 2007

Do you find that you frequently have to resize the columns in your worksheets? Did you know that you can easily set a default column width so that you no longer have to resize your columns?

Follow the steps below to learn how:

  1. Click on the Home tab.
  2. In the Cells group, click on Format.
  3. Click on Default Width.
  4. Key in the default column width.
  5. Click on OK.

To define the default column width for all new workbooks and worksheets, create a workbook template and a worksheet template and then base your new workbooks and worksheets on those templates.

Categories
Excel 2007

How to Draw Lines in Excel 2007

Excel provides you with tools that allow you to create a number of shapes that you could only make use of through a drawing program. Curiously, one of those shapes is the lowly line.

Follow the steps below to learn how to create lines:

  1. On the Ribbon, display the Insert tab.
  2. In the Illustrations group, click the Shapes tool to display a drop-down of drawing objects.
  3. Click the line that is the closest to the type you would like to draw.
  4. Position your mouse cursor where one end of the line is to be located.
  5. Click and hold your mouse button
  6. Drag your mouse until the line is the length of your choosing.
  7. Release your mouse button

That’s all there is to it!

Categories
Excel 2007

How to Enter Dates Without Separators in Excel 2007

If you asked ten different people how they enter dates in their MS Excel worksheets, you would most likely get ten different answers.

When you enter information into a cell Excel will try to deduce what type of information you are entering. For example, if you enter a number like 02262011, Excel will assume you are entering a numeric value and will treat it accordingly. Suppose the number you enter is a date without any separators? Do you think Excel can understand what you are entering? Unfortunately, it cannot.

Why you ask? The answer is because you have given Excel no indication that you want this to be a date. MS Excel keys on separators instead of numeric values. If you cannot change your input habits so that separators are also entered then you need a work-around to convert your entered information to an actual date value.

You may consider using a custom format to display the information such as ##”/”##”/”####.

This format would display the number 02262011 as 2/26/2011. The problem is that it only changes the display of the number. If you want to use the date as a real Excel date, you cannot do so because you have not converted the value into something that MS Excel will recognize as a date.

If the values you have input were very consistent in their format and if they were input as text rather than numeric values, then, there is an easy method whereby you can convert them to dates. When I say “very consistent,” I mean that the input always used two digits for the month and day and four for the year. Also, the cells containing the values must be formatted as text.

Follow the steps below to learn how:

  1. Select the column of dates.
  2. Be certain there is nothing in the column just to the right of the dates.
  3. Display the Data tab of the Ribbon.
  4. Click the Text to Columns tool.

Excel will display the Convert Text to Columns Wizard.

  1. Select the Fixed Width option and click on Next.
  2. Click on Next again.
  3. In the Column Data Format area, select Date.
  4. Select the range in the Destination box, then in your worksheet, click the cell just to the right of the first value you selected above.
  5. Click on Finish.

 

Categories
Excel 2007

How to Turn Off Hyperlink Activation in Excel 2007

If you are like me it most likely bothers you when you enter a URL and it becomes active immediately after you click the Enter key. It always used to bother me until I learned how to prevent it!

When you enter a Web address or a LAN (Local Access Network) server address of a file in an MS Excel worksheet cell, it automatically converts to a hyperlink when you leave that cell.

Follow the steps below:

  1. Click Office Button | Excel Options.
  2. Excel will display the Excel Options dialog box.
  3. Click the AutoCorrect Options button.
  4. Excel will display the AutoCorrect dialog box.
  5. Make certain the AutoFormat As You Type tab is selected.
  6. Deselect the Internet and Network Paths With Hyperlinks check box.
  7. Click OK.