יום ראשון, 14 באוגוסט 2022

Add any number of empty rows to a range


Add any number of empty rows to a range

 

This is a sequel to my original post:
Add alternate empty rows to a range – the coolest method

https://meniporat.blogspot.com/2022/08/add-alternate-empty-rows-to-range.html

 

After publishing it, I thought:

Why not create a generic solution?
Why add only 1 row? Why not 2 or 3 or more?
So I came up with a Parameterized solution:

Put the desired number of empty rows in one formula.

And lo and behold, you have it, like magic.

Attached are three pictures, demonstrating my generic solution:
The first, only one empty row  

The second, two empty rows

The third, three empty rows.

Enjoy 😊


Pic 1 - Add one empty row
 

 

Pic 2 - Add two empty rows

 

 

Pic 3 - Add three empty rows




יום שבת, 13 באוגוסט 2022

Add alternate empty rows to a range – the coolest method



Add alternate empty rows to a range – the coolest method

 

Suppose you have a range of consecutive data in column A and you want to add alternate empty rows between the range's cells.

How can this be done in the shortest, most elegant way?

Well, there are several methods to accomplish this task.

For example, here:
https://www.myofficetricks.com/how-to-insert-alternate-blank-rows-in-excel-spreadsheet/

Or here:
https://www.extendoffice.com/documents/excel/3727-excel-paste-in-alternate-rows.html

 

But they are all time-consuming because they demand several steps.

Here's the coolest method, that uses only one formula:

 

 











 

  

יום שני, 1 באוגוסט 2022

Shortest method to navigate to a worksheet

 

Shortest method to navigate to a worksheet

 

Your workbook is huge. It has dozens of sheets. After working in a certain sheet you need to return to the Master sheet/Index sheet or whatever name you call it.

You could, of course, add a hyperlink to the master sheet from each and every sheet but that, of course, isn't practical.

On the other hand, the "classical" method (as shown in the next two pictures) is also a cumbersome and time-consuming two-phase process.

    


            Old method 1: right-click the navigation icon < to display the worksheets' list

 



                    Old method 2: scroll through the list. When you reach the sheet press OK

 

So what's the best solution?

Creating a named range and using it with one click
😊

1.   Press CTRL+F3 to go to the Name Manager


          CTRL+F3: Opening the Name Manager


 

2.   Press Alt+N (or simply click the New… button) to define a new name

 

3.   In the New Name pane that opens, click: O (for One) in the name. Of course, you can choose any name. I chose the shortest 'cause I'm lazy 😊

 

4.     In the Refers to type: =INDEX!$A$1 (or: Sheet1!$A$1 or whatever the name of your master sheet is) and press: OK



Defining the shortcut to the INDEX worksheet

 

Now, from any worksheet, in order to navigate to the INDEX sheet, just:
1. CTRL+G (or F5) to open the go to pane

2. Type: O

3. Press: OK





                                 Fastest navigation to the target sheet



If you're nimble enough, you can do it in less than 200 milliseconds !!!!

Do you have a faster way?

 



 

 


תוכנה שפיתחתי ב"אקסל" – מתרגמת סכומים למילים בעברית

 

 

תוכנה שפיתחתי ב"אקסל" – מתרגמת סכומים למילים בעברית


רוצים לתרגם סכומים למילים בעברית?

תוכנה שפיתחתי ב"אקסל" מתרגמת סכומים לעברית:

משקל אחד ועד 999 מיליארד.

צפו בסרטון המצורף.

מעוניינים?

צרו אתי קשר בנייד המופיע בסרטון

אה, עיקר שכחתי:

חובה להדליק רמקולים 

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

 

 


יום חמישי, 28 ביולי 2022

How to split a multi-line cell into separate cells



How to split a multi-line cell into separate cells?

There are at least two known solutions to this problem: splitting multiple lines within a cell to separate cells, each containing one line of the original, multiple-line cell.

1.    Text-to-Columns
This method uses the delimiter CTRL+J.
Typing these two keys together, creates the linefeed (hidden) character.

 

2.    Power Query

In Power Query, you choose:
Split Column->By Delimiter->split using special characters->

Insert special character->linefeed

 

But what I want to show you today is a method I've invented, which is (I believe) both the fastest and the simplest.
The solution can "materialize" thanks to the new TEXTSPLIT function in Excel 365 (I'm an "Office Insider" user but I believe that this function is already available to all 365 users, and if not, it will soon be).

So, without further ado let's dive into my solution.

1.    Our  multi-line cell looks like this (Cell A1):




     PIC 1: Cell A1 is multi-line and we want to split each line into a separate cell

 

 

2.    We define the delimiter. In cell I1 we type: =CHAR(10)

CHAR(10) is the ASCII linefeed character.

 

                                       PIC 2: defining the delimiter

 

3.    In cell B4, we type the following formula:

=TEXTSPLIT(A1, I1)

And the contents of cell A1 (5 lines) are split into 5 cells: B4-F4.

 

                                       PIC 3: splitting horizontally

 

And of course, we can split vertically as well:

 


                   PIC 4: splitting vertically

 

That's all, folks.

Hope you liked it.

 



יום רביעי, 27 ביולי 2022

A simple trick that will save you a lot of time

 

A simple trick that will save you a lot of time

 

Suppose you have a digit in each of the cells: A1, B1, C1

And you want to combine the digits into a number:



 



You could do it thus:







Or thus:





 

 

But a far better way would be:

 





Why?
The latter solution is dynamic. If you change the range of cells to be combined, you don't touch the formula. You only modify the Parameter.






 







יום שני, 25 ביולי 2022

Five Excel Table Shortcuts worth Knowing

 

          Five Excel Table Shortcuts worth Knowing

 

1.    CTRL+T (or: CTRL+L a lesser-known shortcut):
Creates a table

2.    CTRL+SHIFT+8 (CTRL+*):
Selects the entire table,
including the header row and the total row (if present)

3.    CTRL+SHIFT+T (a toggle shortcut):
Adds/removes the total row

4.    CTRL+A:
Selects the entire table, without the total row (if present)

5.    CTRL+SHIFT+drag mouse:
Duplicates the entire table