EXCELL:
shortcust:
shift+tab --> go back a cell on line
shift+enter on column
home go to fors clumn at row
Ctrl+home go to (A1)
end last line that have data
PageUp/down next sheet/a sheet before
->/<- first/last line on table
backspace to active cell
shift+8 choose the table
G goto
A choose all
* choose area of active cell(the table itself)
z go back 1 step
y go 1 step on
space select clumn
enter will fix cell without getting down a row
: current hour
; date
end+enter last line
alt+page down/up scroll right/left at sheet
enter line down in a cell
F2 go to edit text
shift+space select line
+-->/<-- choose a few cells
from menu:insert rows,clumns,cells as the number
that was selected before u can copy a data from explorer
and paste it in excell page and right
click make it smart tag refreshable
--> to be on line with the internet.
edit-->clear :content,formats,comments
format-->cells to choose the accurate category
format-->cells | alignment for writing diagonally (offside)
| protection:locked cannot delete or change
hidden will show the data without the formula
than u Must choose Tools|protection|protect sheet!
-to copy a format: click on the small brush icon.
than select the style format thea u made from the sheet
& the selected next cells will get that format.
-format | clumns/row choose the width u like from fixed
size to automatic
autoformat (after u choose the cells)
-sheet | remove/copy
-sharing an excell:tools|sharing area. than tools|
watch changes |light up changes from user2
-copy a table/section : select it go with mouse
to the corner until cursor change its shape to
squere of arrows than push Ctrl and move the cursor where u like.
edit |office clipboard for cutting & pasting
-file | page setup | margins/header+footer
| sheet |what to print,header that
repeats inself clumns to
the right of every sheet
-at spacial paste u can choose to multiply cells than copy|
go where u want to pase | choose the same amount of cells |
choose |edit |spacial paste and:+,-,*,/
formula:
-put = than choose the cell put operator (*,/,-,+) than
another cell
-to copy a formula choose the formula cell & click on the
corner than drag
this will be -relative referral
-fixed referral: u put $ before.for cell B1 to be fixed: $B$1
A$1 --> line is fixed
$A1 --> clumn is fixed
F4 will add $ to 2 dimentions
functions:
-choos a cell | push the Fx |select a function |
select the area or:
=FUNCTION_NAME(B1:B3) or =Average(A3,B10,D3:D10)
<-- with non continuos
names:
-u can select a few cells than on the upper left corner
give them a name(+enter)
or: insert | name | define
than u can =AVERAGE(prices_of_july)
graphs: select the area than F11
lists: start an array than with mouse select more cells &
it will continue
freeze a window:
select cells u want to freeze or stand 1 line down+1 row to the
left of what u wanna freeze and show among every page|
window|un/freeze pane
sorting:
-------
choose 1 cell or the selected cells | from data choose Sort
automatic sort:data |filter |auto filter:
this will open comboboxes on first line
or:data|filter | show all(or auto filter)
functions:
---------
if
-counta --> if cell is full
-countif(A:A,A2)<2 range+criteria
all clumn ^ ^-if u count value less than 2 times =true
-sumif counts the functions that have criterias
-today,now current date,hour every excell refresh
-text | concatinate (~will make 2 words 1)
-countblank count empty cells
-IF(AND(F$>22,F4<44),"ok","not-ok")
-format |conditioned | select a cell |equal to |
than choose template and it will put colors inside the true values
than| add (for a fail condition)|choose the fail
word than add red background data:
----
-data | import outsider data | new internet quest |
choose from the browser the table/chart from your site |
give it a name |ok.
-data | pivote table/pivot chart to make your own table
-data | add middle summery
exercise:
1. multiplication table: B$1*$A2
C B A
1,2,3---^ ^ 2 1 1
1 _ _ 1 2
2 _ _ 2 3
3 _ _ . .
2. from mor levi change 3rd row with $ 128 to nis:
=IF(ISERROR(VALUE(REPLACE(C4,1,2,""))),"",
VALUE(REPLACE(C4,1,2,""))*1.18*4.6)
^ first char-^ ^-2 chars
|-- makes a number from string 128
err:when u do a macro:run-time error 1004 :
at tools |macro |security |low
|