Excel



To adjust column length according to its data

double click on the line at the end of the column name, where the next column begins.

To enter the content of a table
to enter data to go to next cell horizontally use tab
to enter next line press enter, it will automatically go to the cell right below the one you first pressed the tab

To copy a formula of a cell to the neighboring cells hold the mouse pointer at the right-bottom point of the cell, the pointer will turn to a small plus sign, drag the plus sign where you want and the formula will be replicated to the cells based on the value of the refreences you have used
($means static reference, and if you miss it, the reference will be translated by the number of rows and columns that you have moved the formula)

CTRL + ;                    Current date
CTRL + SHIFT + ;       Current time
CTRL + mouse Drag (right bottom sign of a cell)         switch if you want the literal value of the current cell or you want its increments to be copied

$ means the item infront of it is static and should not change if the formula is copied some where else.
& to use reference to a variable in a string

=SUMIFS($H:$H,$B:$B, "="&$B28,$F:$F, "=1")        // sum the numbers in column H, if on column B the item at the same row is equal to the value of B28 and also if the on column F the item at the same row has the value 1

=SUMIF($B:$B, "="&$B28,$H:$H)


Matrix multiplication and multi-cell array formula
If Array1 is A1:B2
Array2 is D1:E2

Highlight a 2x2 blank area

In the formula bar type in =MMULT(A1:B2,D1:E2)
Then this is the important part: instead of enter, use ctrl+shift+enter. The formula will be entered as an multi-cell array formula and it will look like this
{=MMULT(A1:B2,D1:E2)}

You can't manually type in the braces. You have to use the ctrl+shift+enter method.



To remove all hyper links from a sheet
press Alt + F11 click Insert -> Module paste the following code then just click the play
 button at the toolbar to run the code. Source

Sub ZapHyperlinks()
Cells.Hyperlinks.Delete
End Sub



















Subpages (1): Histogram
Comments