Unlimited M.S. Excel Advanced Tips

 Excel Unlimited

_____________________________A_____________________________

• Age Calculator in M.S. Excel

Date of Birth

Current Date (till which date you want to calculate age) =Today()

Years                       =datedif(C4,C5,"Y")

Months                     =datedif(C4,C5,"YM")

Days                          =datedif(C4,C5,"MD")

_____________________________B_____________________________

• BRACKETS

How insert brackets in excel formula's

Square Brackets [  ], Alt091 & Alt093

Curly brackets { }, Alt123 & Alt125

_____________________________C_____________________________

• Copy

Copy text and numbers separately from same column

Select Column data

Press f5 > Click Special

Click constants > check box Text

Copy and paste at desired place

then repeat same procedure and check the number box

and copy and paste numbers now at desired place.

_____________________________D_____________________________

• Date

Enter dates up to requirement

Enter first date

Then click > Alt+H+F+I+S

Now click > Columns

In Type Click > Date

In Date Unit Click > Day

Set Start and Stop Value as per your requirement

Similarly you can add weekday

and Months or Years

_____________________________E_____________________________

_____________________________F_____________________________

• How to Create Form in M.S. Excel

Type your requisite informative 

such as

Name

Father Name

Etc

Now click on format cell

click Custom

Type @\:*_

and then match this formatting to rest of all columns

_____________________________G_____________________________

_____________________________H_____________________________

_____________________________I_____________________________

_____________________________J_____________________________

_____________________________K_____________________________

_____________________________L_____________________________

_____________________________M_____________________________

_____________________________N_____________________________

_____________________________O_____________________________

_____________________________P_____________________________

_____________________________Q_____________________________

_____________________________R_____________________________

Row Function

This is good serial number applying function, as this won't disturbed whenever you delete or insert rows.

Insert Row function from where you want to start your serial number

Now simply add minus rows to get serial number

For example if you are starting serial from second row then minus 1

If you are starting serial from 5th row, then minus 4

And copy this formula to the end of your date table 

• Setting Up Automatic Serial Numbers in Excel

When type name Then it returns Serial Number Value

Formula is =IF(B4<>"", ROW()-3, "")

________________________________S________________________________

• Serial Number Tips

Serial Number is routine issue in excel sheets, whenever you add or delete rows the serial become disturbed. Here are some tips for permanent solution of serial number application.

Sequence Function

In serial column at first row from where you want to start serial Number

Enter sequence function as =sequence(counta(b2:b20))

Here b20 is optional, you can set this up to your desired row number.

This is best solution, but unfortunately, currently, it is only available in Microsoft 365.

Serial Numbers

Type 1 in first row from where you want to start serial numbers

Type Alt+HFIS

check columns

Set start value and stop value > OK

But this function has a issue, that, when you will delete or insert row the serial number will be disturbed

• Spinner

Add Spinner to Excel

First you have to show developer tab in Ribbon, if it is already not shown.

Go to File > Options > Customise Ribbon > Check Developer Tab > Ok

Now from developer tab insert > Spinner > Adjust Size as cell > ok

Now right click on Spinner > Format Control

This dialogue box will open.

From this Go to Control Tab > Select the link of cell > OK

Now Quantity column value would be up down by spin button.

In next cell enter the rate and in amount column set the formula Quantity x Rate = Amount =E6*D6

Now select the row C to F > Copy > Paste in rest rows

Note: You have to link cells with spinner manually with the help of 

Right Click on Spinner > Format Control > Control Tab > Link Cell > OK

Repeat this for all rows

________________________________T________________________________

________________________________U________________________________

________________________________V________________________________

________________________________W________________________________

• Wrap Rows

Go to function bar and insert wraprows function

or type formula in desired cell =WRAPROWS(A1:A10,4)

Here in formula first is function 

In brackets A1:A10 is Column data range

and 4 is the range which you want to adjust for wrap

Unfortunately this function is not available older versions of Excel

But you can use index function for it

=INDEX($A$1:$A$10, ROW(A1) + (COLUMN(A1)-1)*3)

________________________________X________________________________

• XLOOKUP

This formula provides you horizontal data from a vertical column's table.

=XLOOKUP(B8,B3:B6,C3:C6+D3:D6+E3:E6)

Now which name you will type in Cell E8, the formula will give you total of that name.

The bad thing is that this function is't available in older than Excel 2019 versions. But the good thing is that you can use it online excel spreadsheet at Microsoft website. 

Microsoft Excel

you can here use latest excel, make spreadsheets and save them on Microsoft Drive. Microsoft gives you 5 GB free space. That could be enough to use latest functions.

________________________________Y________________________________

________________________________Z________________________________