Category Archives: Using Excel

In Excel, subtract or add time values

If you want to add or subtract times, as in hours and minutes, in Excel, you do it in exactly the same way as you manipulate other numbers, but you must tell Excel you are working in hours and minutes by formatting the cells as hh:mm (or one of the other time formats).

To do this, select the cells you are going to use, right click, format, and choose hh:mm (or one of the other time formats).

Then, subtracting 9:02 from 16:30 will give you 7:28.

 

Format Painter in Excel

I can never remember how to use the Format Painter in Excel.

Select the shape, text, or cell that has the format which you want to copy.

(Excel may then show the Format Painter symbol – a paint brush, but if it doesn’t then…)

On the Home tab, click on Format Painter (double click if you want to copy to multiple locations)

Select the shape, text, or cell that needs to be formatted – you can drag the pointer across a range of cells if you want to.

To stop formatting, press ESC.

To copy a column width, select the heading of the first column, click Format Painter, then click on the heading of the column you want to apply the width to.

In Excel to Delete or Add (Insert) a row

To Delete a row in Excel:

In your open spreadsheet, find the row you want to delete, and highlight it :- to do this, move the cursor to the left hand edge of the spreadsheet, and click on the number of the row.  So in the picture below, to delete the row containing ‘Web Design’ click on the number 22 on the far left.insert delete

 

(To increase the size of this picture, click on it)

Now, EITHER
right click with the mouse in the same place, and choose Delete
OR
go to the top ribbon and click on Delete

To add or Insert a row in Excel:

In your open spreadsheet, find the place where you want to add the row, and highlight the row below where you want the new one :- to do this, move the cursor to the left hand edge of the spreadsheet, and click on the number of the row.  So in the picture above, to add a new row above the row containing ‘Web Design’ click on the number 22 on the far left.

Now, EITHER
right click with the mouse in the same place, and choose Insert
OR
go to the top ribbon and click on Insert

Using Freeze Panes in Excel Spreadsheets

When you are working on a spreadsheet, it can be really irritating if your column titles disappear off the top of the page as you scroll down.

To keep column titles in view all the time, use Freeze Panes.

Click on the view tab to get the Freeze Panes option, and click on the little arrow next to it. You have three choices – to freeze the top row, freeze the first column (useful if you are scrolling off to the right), or Freeze Panes.  If you click in to a cell, then choose Freeze Panes, the rows above that cell and the columns to the left of that cell will remain in view all the time while the rest of the spreadsheet scrolls.

Linked spreadsheets

In Excel 2010 the Edit Links command has moved – you need to go to the Data tab, and choose Edit Links in the Connections area of the tool bar.

When would you use this?  If you have set up spreadsheets that link to each other – in other words they pull in data from other spreadsheets – and for some reason one was re-named, or re-located, you use Edit Links to update the necessary information.

If you have donor files which supply data to a receiver file (so the receiver file is the one which contains  =[location]data ) then if you save the receiver file FIRST, the links should update automatically anyway.

Use data from one spreadsheet (or workbook) in a different one

There are times when you may use one spreadsheet, or workbook, to pull together data from other spreadsheets.

To link sheets in the same workbook:

Click into the destination cell, type = (an equals sign) then go to the other sheet and click into the cell that contains the information you want (the donor cell), and hit enter.  (It makes sense to give each sheet a name before doing this.)

To link sheets in different workbooks (or Excel files):

Have both workbooks (files) open; click into the destination cell, type = (an equals sign) then go to the other workbook (file) click into the cell that contains the information you want and hit enter.

This is described below using some screenshots to illustrate.

Workbook with named sheets

A sheet for each day of the week

An Excel Workbook (or file) has more than one sheet.  In this picture I have set up sheets to record what I spend on each day of the week.

 

 

 

 

 

 

Week summary sheet

Week summary sheet

My final sheet pulls together the total for each day.

 

 

 

 

Linking to another sheet

Linking to another sheet

To get the total for each day, I have linked back to the sheet for each day.

Cell B9 in this sheet, links to Monday’s sheet by the command =Monday!C14 as you can see from looking in the command line box.  The cell C14 in sheet Monday contains the total for Monday, and now I have pulled that in to my week summary.  To do this, I clicked on cell B9 of Week, typed = then went to the Monday sheet, clicked into cell C14 and hit enter.

 

 

There is also another workbook (file) which pulls the weekly totals into a monthly summary.

Link to a different workbook

Link to a different workbook

 

In this case, the command line contains the full path to the workbook (file) containing the donor cell and the data of interest: =’C:\Users\Mail\Documents\[pastelink.xlsx]Week’!$F$13