MS Excel 2013 Tips and Tricks – Part 1

MS excel 2013 tips and tricks

T&T 1: Putting your own currency

Excel 2013 does not have default Nepali rupees symbol. But it has ‘NPR’ prefix and other currencies. But if we want to prefix some other string, for example ‘NRs.’ we can do it as :

1.Select the column or any range of cells you want to add prefix  to

1

2.  Select the small arrow button from the Number Ribbon to open the Format cells dialog box. (See Picture)

2

3.. Choose Custom type from the Category.

3

4.. In the Type field, choose your desired number format from the list and type “NRs. ” before the number format. (Don’t forget to write double quotations.). And choose Ok. This will prefix the string NRs. to all the values of the column.

4

5. Click Ok

5

6. From then, any values you insert will automatically start to have the same prefix in the entire column.

7. To remove this  formatting . Choose Clear .

6

T&T 2: Multiple Sheets select and edit

Suppose you have multiple sheets opened. You need to write something in a cell of all the sheets simultaneously. You can do it as :

7

1. Suppose that you want to write in the cell B9. Select that cell

8

2. Select all the sheets that you desire by pressing Ctrl + Click (Green underline appears)

9
3. Type the thing you want and you are done. The text will be inserted on all the sheets.

T&T 3: Automatic number generation

For Serial number

1, To generate a serial number starting from 1, type 1 in a cell and drag the bottom right corner of the cell up to the desired range holding Ctrl key on the keyboard.

1011

To generate consecutive odd or even numbers :

1, Type 1 and 3  as below and select both cells. Drag the bottom right corner of the cells pressing Alt on the keyboard.

12

2. To generate even numbers. Type and 2 and 4 and do the same thing.

13

Number Generation with Prefix

1. You can also generate a series even when prefixes are present. Drag the corner holding Alt Key. Experiment yourself to find out new tricks.

14a14b

T&T 4: Name Split and Flash fill

1. Suppose you have a long list of names as given below. You want to separate the first name and last name into two different columns. Instead of trying to separate them by tedious editing, there is a feature in Excel called Flash fill.

15

2. In a new column write the first name of the first entry. By writing this you are training the excel to see a pattern.

16

3. Select the entire column or range where you want to fill.

17

4. Go over the Fill option and click the drop down arrow. Choose Flash Fill

18

19

20

 

These are some the features  in Excel which can make our work faster. Stay tuned for part 2 for similar tips. , /m

1 Comment

  1. great content

    Initially, I believed your headline was misleading, but you probably delivered. Nice job.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *