Anybody good with Excel?

[edd_jedi]

I keep a spreadsheet for my SW purchases, it does the job but could be better! There are some things I would like to be able to do:

1) Fix the header so when I scroll I can still see it
2) Be able to sort by columns (eg price or name) but not cock up the total calculations
3) Automatically calculate average price (currently I have to update the sum manually)

Is anybody here an Excel wiz that could do this for me?

 

[Grant_C]

When I get stuck with these things I type into google or Youtube.

YouTube is good so you can see the actions.

I'll have a look at locking the headers now- the others im not sure.

 

[Grant_C]

Freeze Header

1
•

Open a worksheet in Microsoft Excel. Add row and column header text as desired.

2
•

Click on the column letter on the right side of the column you want locked. This highlights the entire column. Highlight a group of columns by clicking on the first column and dragging the mouse, with the left button depressed, across the desired columns to highlight.


3
•

Click on the "View" tab if using Microsoft Excel 2007. Under the Window group item in the menu, click on the arrow for "Freeze Panes." Select the option to "Freeze First Column" to lock only the one column highlighted. Select the option to "Freeze Panes" if you want to freeze a group of panes at one time.

4
•

Click on the "Window" menu if using Microsoft Excel 2003 and select "Freeze Panes" to lock one or a group of columns.

5
•

Repeat the process listed in steps 1 through 4 to unlock the column by selecting "Unfreeze Panes."



Read more : http://www.ehow.com/how_6779013_lock-column-headers-microsoft-excel.html

 

[edd_jedi]

Thanks Grant, was slightly different in my Mac version but managed to find it! Didn't know they referred to it as freezing.

 

[Richard_H]

1. Freeze panes option
2. Sort and filter option. Sort on column then click expand selection.
3. Simple function =average(a1:a100)

Obviously change a1 and a100 to the first and last cells you want to average

 

[Clarkspie&chips]

If you highlight a whole column (or row) you get the average, total, total number of entries and a few other things u cant remember all called up at the bottom.