יום ראשון, 14 במאי 2023

20 CTRL+SHIFT keyboard shortcuts you are probably not familiar with

 

20 CTRL+SHIFT keyboard shortcuts you are probably not familiar with

1)   CTRL+SHIFT+8 => Selects the entire table,
including the header row and the total row (if present)


2)   CTRL+SHIFT+T => (a toggle shortcut):
Adds/removes the total row of a table


3)   CTRL+SHIFT+U => Expands/contracts the Formula Bar (toggle)


4)   CTRL+SHIFT+9 => unhides hidden rows within selection


5)   CTRL+SHIFT+L => activates/deactivates filtering (toggle)


6)   CTRL+SHIFT+F6 => toggles to the previous workbook (when two or more workbooks are open)


7)   CTRL+SHIFT+F4 => Repeats the last search (like: Find Next)  - but backwards (upwards)


8)   CTRL+SHIFT+< => copies values from cell above (same as: CTRL+D) but without selecting the cell above (works on more than one cell)


9)   CTRL+SHIFT+> => copies values from cell to the left (same as: CTRL+R) but without selecting the cell to the left (works on more than one cell)


10)  CTRL+SHIFT+SPACEBAR => same as CTRL+A


 

11)  CTRL+SHIFT+O  => selects all cells containing comments (notes in 365)

 

12)  CTRL+SHIFT+PAGEDOWN => Selects the current and the next worksheet (creation of a Group)

 

13)  CTRL+SHIFT+PAGEUP => Selects the current and the previous worksheet (creation of a Group)

 

14)  CTRL+SHIFT+[ => Selects all Precedents (Direct & Indirect)

15)  CTRL+SHIFT+] => Selects all Dependents (Direct & Indirect)

 

16)  CTRL+SHIFT+7 => Applies border (single, black, thin) to selection (only outer border is applied)

 

17)  CTRL+SHIFT+- => removes border (single, black, thin) from selection (removes all borders within the selection)

 

18)  CTRL+SHIFT+A =>  (when the insertion point is next to the function's name entered) Displays the function’s arguments (in parentheses)

 

19)  CTRL+SHIFT+' => Copies the value from the cell above the active cell into the active cell. If the cell contains a formula, it is converted into value.

 

20)  CTRL+SHIFT+. => Copies the contents of the adjacent cell (to the left) into the active cell. If the adjacent cell contains a formula it copies the formula with relative references.

 

 


יום חמישי, 4 במאי 2023

Excel - How to calculate milliseconds

 

Milliseconds in Excel

 

We all know how to calculate difference in time: in hours, minutes and seconds.

But did you know that you can compute time with milliseconds in Excel as well?

In order to calculate milliseconds, you first need to format the number properly.

This won’t do:

                Pic 1: we cannot subtract times with milliseconds…

So how do we do it?
First, we need to convert the data structure.

The last (third) colon :  must be converted into a dot .

This can easily be accomplished with the SUBSTITUTE function, as can be seen in cells D2 & E2.

             Pic 2: changing the data


Next, we have to format the Start time, End time and the Difference as can be seen in this screenshot:

           Pic 3: changing the number format

 

And, now in cell G2 we get the correct result, in hours, minutes, seconds and milliseconds:

           Pic 4: the desired result with milliseconds