You are here: Home

How to sum non-adjacent cells

January 19, 2011 By

Suppose you have a rang of cells, say A1:A20, and you want to sum only cells A1, A4 , A6, A7and A8 which are non-adjacent. You can write a formula like this:

=A1+A4+A6+A7+A8

This is OK for a few cells but for large range, it is better to write the formula as follows:

=SUM(A1,A4,A6:A8)

Which means some A1, A4 and the range A6 to A8.

Filed Under: Excel 2007, Excel 2010, Formulas, Functions Tagged With: , , ,

How to highlight duplicate values using conditional formatting

January 14, 2011 By

You can highlight duplicate values in a data range by using conditional formatting. Follow these steps:

  1. Select your data range.
  2. In the Home tab, Styles group click Conditional formatting.
  3. Point to Highlight Cells Rules.
  4. In the sub-menu to the right click Duplicate Values.

  5. The Duplicate Values window will open. Click OK.
  6. Any duplicate values in the range will be highlighted with the default color. You can change the color before clicking OK.

Applies to: Excel 2007, Excel 2010

Filed Under: Excel 2007, Excel 2010, Formatting Tagged With: , ,

How to save excel worksheet as a comma delimited file

January 6, 2011 By

You can save an Excel worksheet as a comma delimited file. I.e. the worksheet columns will be saved in the file separated by commas. This will allow you or someone else who has no Excel, to browse the file in any text editor.

To save the worksheet as a comma delimited file:

1.       In Excel menu click File.

2.       Click Save As. The Save As window will open.

3.       In the Save as type drop down, select CSV (Comma delimited).

4.       Any row in the worksheet will be transformed like this:

“ABC;7268;87368;XYZ;373″

The delimiter is either a “,” or “;” depending on your installation of Excel.

Applies to: Excel 2007, Excel 2010

Filed Under: Excel 2007, Excel 2010, General Tagged With: , ,

How to delete sparklines

December 9, 2010 By

If you want to delete or clear a sparkline then follow these steps:

  1. Select the sparkline or the group of sparklines that you want to delete.
  2. On the Sparkline Tools, click the Design tab.

  3. On the Design tab, in the Group group, click Clear.

Filed Under: Charts, Excel 2010 Tagged With: , ,

How to hide and unhide worksheets in Excel 2007

November 25, 2010 By

If you have a workbook with multiple worksheets and you want to hide some worksheets, e.g. to protect data from accidental change, you can do that as follows:

  1. Select the worksheet(s) you want to hide.
  2. In the Home tab, Cells group click Format.
  3. Under Visibility, point to Hide & Unhide and then click Hide Sheet.

To display or unhide a worksheet:

  1. In the Home tab, Cells group click Format.
  2. Under Visibility, point to Hide & Unhide and then click Unhide Sheet.
  3. In the Unhide sheet box, double-click the name of the hidden sheet that you want to display.

Applies to: Excel 2007, Excel 2010

Filed Under: Excel 2007, Excel 2010, Formatting Tagged With: , , ,

How to show or hide data markers in a sparkline

November 12, 2010 By

If you are using a sparkline with a line style (Excel 2010), then you can show data markers to highlight individual values. Follow these steps:

  1. Select a sparkline or a group of sparklines.
  2. On the Sparkline Tools, click the Design tab.
  3. On the Design tab, in the Show group, select the check boxes for the individual markers you want to show e.g. High Point, Low Point or Negative Points.
  4. To show all markers, select the check box labeled Markers.
  5. To hide a specific marker or markers uncheck the relevant check box(s).

Filed Under: Charts, Excel 2010 Tagged With: , ,

How to create Sparklines in Excel 2010

November 5, 2010 By

A Sparkline is a tiny chart that can be inserted in a single cell. If you have data presented in a column or a row, then a Sparkline could be very useful in showing the pattern or the trend of that data.

The example below shows a Sparkline for the data in row 2.

To insert a Sparkline follow these steps:

  1. Enter your data in the worksheet.
  2. Select the cell where you want to insert the Sparkline.
  3. Click on the Insert tab.
  4. In the Sparklines group   select one of the three options:
    1. Line: to plot your chart as a trendline.
    2. Column: to plot your chart as a series of columns.
    3. Win/Loss:  good if you have negative numbers.
  5. The Create Sparklines window will open.
  6. In the Data Range text box enter your data range (in the form B2:G2).
  7. Your Sparkline will be inserted in the specified cell.

Tip.

If you right-click on the Sparkline you will see Sparkline options. e.g. you can change the data range or the Sparkline location.

Filed Under: Charts, Excel 2010 Tagged With: , ,

How to clear conditional formatting in Excel 2007

September 27, 2010 By

To clear conditional formatting from a range of cells follow these steps:

  1. Select the cells for which you want to clear conditional formatting.
  2. On the Home tab, Styles group, click Conditional formatting.
  3. Click Clear Rules.
  4. From the menu to the right Click Clear Rules from Selected Cells.

To clear conditional formatting from the whole worksheet (no selection is required):

  1. On the Home tab, Styles group, click Conditional formatting.
  2. Click Clear Rules.
  3. From the menu to the right Click Clear Rules from Entire Sheet.

Filed Under: Excel 2007, Formatting Tagged With: ,

How to attach a file to Excel 2007 workbook

September 12, 2010 By

It is fairly easy to  insert a file into an  Excel worksheet as an attachment. That file can be a word document, PowerPoint presentation or even another Excel workbook.

Follow these steps:

  1. In Excel Insert tab, Text group click Object.

  2. The Object window will open.

  3. Select Create from File tab.
  4. Select the check box Display as Icon.
  5. Click Browse and select the file you want to attach.
  6. The file will be shown on the worksheet as an icon.

Filed Under: Excel 2007, General Tagged With: ,

AutoFit row heights in Excel 2007

August 2, 2010 By

If you have a number of rows in your sheet and each one has a different size of data. For example one row may have a wrapped text or a text with big font size that does not match with  the current row height, then you can adjust the row height.

To adjust the height of each row to fit  the entered data:

  1. Select the rows to be adjusted.
  2. In the Home tab, Cells group, click Format.
  3. From the Format menu Click AutoFit Row Height.

Alternatively you can do the same by following these steps:

  1. Select the rows to be adjusted.
  2. Point to the lower border of anyone of the selected rows (at the left of the row where the row number is). The mouse pointer should change to a cross with vertical arrows at the top and bottom.
  3. Double click on the row border.

Before:

After:

Filed Under: Excel 2007, Formatting Tagged With: , ,
« Older Posts
Newer Posts »