How to delete sparklines

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.

How to hide and unhide worksheets in Excel 2007

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

How to show or hide data markers in a sparkline

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).

How to create Sparklines in Excel 2010

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.

AutoFit row heights in Excel 2007

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:

AutoFit Column widths in Excel 2007

You may have a number of columns in your sheet and each one has a different size of data. For example one column may have only a 2 digit sequence number but another column has a long text.

To adjust the size of each column to fit  the entered data:

  1. Select the columns to be adjusted.
  2. In the Home tab, Cells group, click Format.
  3. From the Format menu Click AutoFit Column Width.

Alternatively you can do the same by following these steps:

  1. Select the columns to be adjusted.
  2. Point to the right border of anyone of the selected columns (at the top of the column where the column label is). The mouse pointer should change to a cross with horizontal arrows to the left and right.
  3. Double click on the column border.

Before:

After:

How to insert a shape in Excel 2007

To insert a shape (rectangle, circle, line … etc.) follow these steps:

  1. Select the Insert tab, then Click Shapes.
  2. XL7Shapes1

  3. A drop down menu will show different types of shapes.
  4. XL7Shapes2

  5. Select the shape you want. The mouse pointer will change to a cross like this :XL7Shapes3
  6. Draw the shape by pressing  the left mouse button then dragging the mouse.
  7. Release the left mouse button when finished.

How to summarize data using a pivot table in Excel 2007

Pivot table is a feature of Excel that takes a long list of data, summarizes it into a shorter list that is more easy to analyze and assimilate.

The best way to explain how to create a pivot table in Excel 2007 is by using an example.

Below is a list of products, salesmen and total sales for a product / salesman.

XL7Pivot1

To create a pivot table to summarize the total sales by product, you perform the following steps:

  1. Prepare your data table and make sure you have columns headings.
  2. Select the data range or simply click on a cell within the range.
  3. In the Insert tab click Pivot Table. The window Create Pivot Table will open.
  4. XL7Pivot2

  5. Make sure that Table/Range points to your correct range then click OK.
  6. Now you will get a rectangular area to the left that represents the pivot table, and a field list to the right.
  7. XL7Pivot3

  8. In the Pivot Table Field List to the right select Product and Total Sales. The pivot table that summarizes total sales by product will be created as follows:
  9. XL7Pivot4

  10. If you select Salesman instead of Product, this is what you get: a pivot table that summarizes total sales by salesman.
  11. XL7Pivot5

  12. You can plot a chart from the pivot table as shown below.

XL7Pivot6

Hide rows based on cell value in Excel 2007

To hide rows based on cell value in Excel 2007 follow these steps:

  1. Prepare your data including a header row.
  2. In the Home tab, Editing group, click Sort and Filter then select Filter from the menu.
  3. Your data will now look like this:

XL7HideRows1

In this example we want to hide rows where the sales are greater than or equal to 2000. I.e. we will only show rows where the sales are less than 2000.

  1. Click on the filter arrow next to Sales.
  2. Point to Number Filters and select Less Than.
  3. XL7HideRows2

  4. The Custom AutoFilter window will open.
  5. XL7HideRows3

  6. Enter 2000 in the Combo box to the right and click OK.
  7. Now all rows with values greater than or equal to 2000 will be hidden. Here is the final result:

XL7HideRows4

Note:

If your data is text then you will see Text Filters instead of Number Filters in the menu.

How to some times greater than 24 hours in Excel 2007

To sum times you usually use Excel built-in function SUM. If you have three cells A1, A2 and A3 formatted as time (hh:mm), and you have :

A1 = 02:30

A2 = 10:15

A3 has the formula: =SUM(A1:A2)

then A3 will have the correct values of 12:45.

However if your total times are greater than 24 hours then things get tricky.

If you have :

A1 = 15:30 and A2 = 10:15 then A3 will have 01:45 instead of 25:45. I.e. any hours beyond 24 hours are dropped.

To retain the hours beyond 24, you have two alternatives:

1. If you want to convert the hours beyond 24 into days then use this cell format : dd:hh:mm. The total in the previous example will be 01:01:45 ( 1day , 1 hour and 45 minutes)

2. If you want to keep the hours beyond 24 as is then use this cell format :[h]:mm. The total in the previous example will be 25:45.

In both cases the formula will remain as it is. Only the cell format differs.

See another example below:

More posts on time:

http://www.exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times/
http://www.exceldigest.com/myblog/2008/12/04/how-to-create-a-custom-time-format/