Twenty KB shortcuts In Excel 365 using the SHIFT key

Excel 365 - 20 KB shortcuts using the SHIFT key

1.  SHIFT+PAGE UP

Extends selection one screen upwards. For example, if your active cell is N31, this action selects cells N1:N31.

 

2. SHIFT+PAGE DOWN

Extends selection one screen downwards. For example, if your active cell is C1, this action selects cells C1:C31.

 

3. SHIFT+HOME

Selects from active cell to first column on the same row. For example, if your active cell is G4, this action selects cells A4:G4.

 

4. SHIFT+SPACEBAR/CTRL+SPACEBAR

In a table:

a. Pressing SHIFT+SPACEBAR when the active cell is in a table – selects the entire record.

b. If you now press CTRL+SPACEBAR - selects the entire table

c. you can select the whole table by pressing: CTRL+SHIFT+SPACEBAR in any cell within the table

 

Outside a table:

a. Pressing SHIFT+SPACEBAR when the active cell is not in a table - selects the entire row.

b. Pressing CTRL+SPACEBAR when the active cell is not in a table - selects the entire column.

c. you can select the whole sheet by pressing: CTRL+SHIFT+SPACEBAR in any cell in the sheet.

 

5. SHIFT+F11

Inserts a new worksheet

6. ALT+SHIFT+F1

Inserts a new worksheet (same as 5.)

7. CTRL+SHIFT+9
Unhides hidden rows within selection

 

8. SHIFT+F7
Opens the Thesaurus pane

  

9. CTRL+SHIFT+F1 (toggle)
Hides/unhides the Title bar, QAT (Quick Access Toolbar), Menu bar and Ribbon

 

10. SHIFT+F6 (or: F6)
Toggles between: Menu bar, Active sheet and Name box

  

11. CTRL+SHIFT+F6
Toggles between open workbooks (when two or more workbooks are open).

 

12. CTRL+SHIFT+O
Select all cells with comments 

 

13. CTRL+SHIFT+Home
Extends selection from the active cell to A1. For example, if the active cell is G17,the range: A1:G17 is selected.

 

14. END->SHIFT->Home
Selects the last non-empty cell in the sheet

15. END->SHIFT->Enter
Selects the intersection of the last non-empty column in the sheet
with the active cell’s row.

For example, if the active cell is A19 and the last non-empty cell is S26, then this keyboard combination will select cell: S19.

16. SHIFT+BACKSPACE
Selects the first cell In a selected range

17. CTRL+SHIFT+[
Selects all Precedents (Direct & Indirect)
(same as: Formulas->Formula Auditing-> Trace Precedents, but without the arrows)

18. CTRL+SHIFT+]
Selects all Dependents (Direct & Indirect)
(same as: Formulas->Formula Auditing-> Trace Dependents, but without the arrows)

19. SHIFT+Enter
After entering a cell address in the Name Box, selects the entire range: from the active cell to the cell whose address was entered in the Name Box.(for example, if your active cell is A2 and you key: A20 in the Name Box and then press SHIFT+Enter – Excel selects the range A2:A20)

20. SHIFT+F2
Adds a new comment/edits an existing comment in the active cell (bear in mind: this refers to “comments”, not to “notes” which are new to Excel 365).

 

Excel - How to create an Index Worksheet

 

How to Create an Index Worksheet
(Easy access to any worksheet in your workbook)

Sometimes your workbook has dozens of sheets and you want to easily navigate to a certain sheet without scanning through the entire list.

In that case, you need to build an INDEX sheet that will facilitate this process and save you a great amount of time.

1)    Create a new worksheet whose name is INDEX and make it the first worksheet of the workbook.

2)    Define a named range in the Name Manager. I called mine “TabNames” but you can choose any valid name.

In the “Refers to” text box, add this formula:

=LET(a, GET.WORKBOOK(1),MID(a, FIND("]",a)+1,255))  

         Pic 1: defining the TabNames formula in the Name Manager

 

          Pic 2: the TabNames formula in the Name Manager

3)    In cell A1 type:

=TRANSPOSE(TabNames)

This will create the list of the file’s sheets in column A, starting from cell A2

   Pic 3: creating the list of sheets in column A

 4)    Type the following formula in cell B2: =HYPERLINK("#"&A2&"!A1")

This formula adds the hyperlink to the worksheet whose name appears in the adjacent cell (A2).

 

          Pic 4: Adding the hyperlink for the first sheet

 5)    Double-click the lower right corner of cell B2 to propagate the formula to the rest of the list

                   Pic 5: Adding the hyperlinks to all the file’s worksheets

Now you’re good to go. Whenever you want to “jump” to a specific worksheet, click the cell in column B in order to go directly to that worksheet.

6)    Don’t forget to update the list whenever you add/modify/delete a worksheet. After each modification you need to refresh the list (select cell A1, press F2 and then: Enter)

And one more trick:

In order to return quickly to the INDEX sheet, define a shortcut name in the Name Manager’s Defined Names, for example: O, which refers to  Cell A1 in your INDEX sheet, as can be seen in this screenshot:

Pic 6: Adding the shortcut key to return to the INDEX sheet

 Now, whenever you want to go back to the INDEX sheet, Press F5 (or: CTRL+G) to display the GO TO pane, type: O In the Reference Text Box and press: OK

Pic 7: going back to the INDEX sheet

This will take you immediately back to the INDEX worksheet.

***

BTW, since the file contains a Macro 4.0 command, you’ll have to save it as a macro-enabled workbook (.xlsm) instead of a regular workbook (.xlsx)

 

איך פותרים חידה מתמטית בעזרת אקסל

 

איך פותרים חידה מתמטית בעזרת אקסל?

 

הסבר בסרטון היוטיוב:

https://www.youtube.com/watch?v=FuIajvlpQ0E

 

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.

 

 

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 

Getting rid of all instances of duplicate values

Getting rid of all Instances of duplicate Values 

There are many methods in Excel to get rid of duplicate values: you have 2 instances of the same value and you want to keep only one and get rid of the second, superfluous value.

But what if you want to remove both instances of duplicate values?

So, here’s  trick I invented which allows you to accomplish this in one formula.

In column N we have a spilled array of dates, some of which are duplicate (marked in yellow: 13/04/2021, 15/04/2021, 19/04/2021).

The formula in cell P2 creates an array after removing the above mentioned duplicates. The trick here is to mark each duplicate (found with the COUNTIF function) with an intended “error” so that the TOCOL function will ignore it.

This post demonstrates a case where we have two duplicate instances but of course the same method can easily be applied where there are more than two such instances.

That’s all.

Pic 1: Remove all instances of duplicate values