Doing this is very simple!

Just go to the View tab on the Ribbon, and under the Show section, tick (to show) or untick (to hide) the Gridlines.

Educate Yourself in Excel

*By* rushabhs
*in* Views & Printing
No Comments

Sometimes you find some excel files that do not have the lines of the spreadsheet. Those lines are actually called ‘Gridlines’. People hide these lines for a better look for the sheet, once the entire sheet is made with formulas and stuff.

Doing this is very simple!

Just go to the View tab on the Ribbon, and under the Show section, tick (to show) or untick (to hide) the Gridlines.

Doing this is very simple!

Just go to the View tab on the Ribbon, and under the Show section, tick (to show) or untick (to hide) the Gridlines.

*By* rushabhs
*in* Cell & Data Formatting
6 Comments

This is very simple in MS Word !

But how do you do this in MS Excel?

But how do you do this in MS Excel?

A little longer way but simple once you get it.

Suppose you have data like this: –

Notice that every alternate cell has text in **Bold**.

So now you want to select all cells in the sheet that have the formatting as of that is there in cell A1 (Bold in this case).

1. Go to Home tab on the ribbon – Under Editing section click on Find & Select – Choose Find.

2. Click Options.

3. Click on Format, select Choose Format from Cell.

4. Click on A1.

5. Click on Find All.

6. Click on 1 of those results and then Ctrl + A (Select All).

7. Close the Find dialog box.

Your done! ðŸ™‚ A little long but effective way ðŸ™‚

Keep Excel-ling ðŸ™‚

*By* rushabhs
*in* Formulas, Text Functions
No Comments

Suppose you have a list of mobile nos, with a 0 as prefix.

Now you do not want that 0 to be there, and it should be only a 10 digit mobile number.This is simple!

You can use the =RIGHT formula, which extracts the required number of characters, starting from the right part of your data.

Say you have a mobile number with 11 digits (0 as prefix) in cell A1. You want to remove the 0 and have only the remaining 10 digits in cell B1.

Type this formula in B1,

=RIGHT(A1, 10)

This will extract 10 characters starting from right, from cell A1.

Thinking how to extract from the left side, simple, use =LEFT(text, no_of_characters_to_extract) Â Â ðŸ˜‰

Now you do not want that 0 to be there, and it should be only a 10 digit mobile number.This is simple!

You can use the =RIGHT formula, which extracts the required number of characters, starting from the right part of your data.

Say you have a mobile number with 11 digits (0 as prefix) in cell A1. You want to remove the 0 and have only the remaining 10 digits in cell B1.

Type this formula in B1,

=RIGHT(A1, 10)

This will extract 10 characters starting from right, from cell A1.

Thinking how to extract from the left side, simple, use =LEFT(text, no_of_characters_to_extract) Â Â ðŸ˜‰

*By* rushabhs
*in* Formulas
1 Comment

Suppose you have a list of values as this: –

You want to count all values except “d”. Normal =COUNTA will include all values.

But you can NOT count any 1 value (eg – “d”) using this formula: –

=COUNTIF(A1:A11,”<>d”)

**QUESTION: –**

What if you want to count all values except “c” and “d” both?

Try this out yourselves.

If you get it, send your answer to me on **rjs1212@gmail.com**.

Lets see who gets it first ðŸ™‚

Whoever gets it, I will post that person’s name on the blog.

*By* rushabhs
*in* Date & Time, Formulas
No Comments

Ever tried to get your age directly from your date of birth?!

This is the formula: –=IF(MONTH(TODAY())>MONTH(A2),YEAR(TODAY())-YEAR(A2),IF(AND(MONTH(TODAY())>=MONTH(A2),DAY(TODAY())>=DAY(A2)),YEAR(TODAY())-YEAR(A2),YEAR(TODAY())-YEAR(A2)-1))

Simply put in your Date of birth in A2 cell, and type this formula in any other cell.

You ‘ll get your age ðŸ™‚

This is the formula: –=IF(MONTH(TODAY())>MONTH(A2),YEAR(TODAY())-YEAR(A2),IF(AND(MONTH(TODAY())>=MONTH(A2),DAY(TODAY())>=DAY(A2)),YEAR(TODAY())-YEAR(A2),YEAR(TODAY())-YEAR(A2)-1))

Simply put in your Date of birth in A2 cell, and type this formula in any other cell.

You ‘ll get your age ðŸ™‚

*By* rushabhs
*in* Formulas
1 Comment

This wildcard represents any number of characters in that position.

For example, if you put the criteria as *Mart*, Excel will search for all entries which contains the work Mart; so it could be Martin, FoodMart, etc.

This wildcard represents a single character in that position.

For example, if you put the criteria as Mar?, Excel will search for all entries which have Mar as the first 3 letters and then any 1 letter; so it could be Mark, Mart, Mars, etc.

You can use these wildcards while putting in the criteria using Advanced Filter.

*By* rushabhs
*in* Tips
No Comments

You want to compare data between 2 columns, row-wise.

There is a very simple way to do it.

There is a very simple way to do it.

- Select both columns with data
- Press F5 and select special (alternatively, from home ribbon, click on Find & Select and then choose Goto Special)
- Now, click on
**row differences**and press**OK**. - Excel instantly highlights all the cells in 2nd column that do not match with first column.
- You can simply then change the colour of the font, or apply a fill to the cells, to draw your attention.

You may sometimes also want to highlight all the blank rows in your data, and then delete them in 1 shot.

*By* rushabhs
*in* Best Practices
No Comments

In any formula, where you have to select a dataset, you would questions:

What if any data is modified?

What if new data is added to the rows below my existing data?

Â

Any modification in the data will not have a problem in the output, as it will rerun the formula everything data is modified anywhere.

Â

But when new data being added in below rows, the outputs don’t change as the formula is still using the same dataset. This can be rectified.

For example, you are using a simple formula wherein you are taking a sum of all salaries of all employees.

The salary column is say the H column, and you have a list of 100 employees.

Your formula would look like this =SUM(H1:H101)

(H101 since H1 would be the header of the column).

Now tomorrow 3 new employees are added in the data, so you will need to change the formula to =SUM(H1:H104). So everytime new employees are added, your formula needs to be updated! Crazy! Maddening! Isn’t it?

Simple solution! ðŸ˜‰

When you say H:H, you are basically looking up the entire column for values and not fixed number of rows in that column. Thus, whenever you add any new employees, data of that employee is fetched in the formula automatically and our output changes ðŸ™‚

Â

Useful in all kinds of formula.

Â

Enjoy!

*By* rushabhs
*in* Cell & Data Formatting
1 Comment

Some people actually type a comma between numbers to depict a thousandth value, like 1,000! NEVER do this. If you enter a comma, the number becomes text and then cannot be used in any formulas. Instead type the number as is without any comma’s.

Â

To add the comma’s, simply right-click on the required cells, go to **Format Cells ***(ctrl + 1 is the keyboard shortcut for this)*, under the **Number** tab, select **Number** as the **Category**.

Then check on Use 1000 separator.

Â

You can also select the no. of decimal places you want to have by default, and also select the way negative nos. in the data should look.

Do have a look at the **Sample**Â to make sure you selecting the correct options for the way you want your data to look like.

*By* rushabhs
*in* Sort & Filter
No Comments

Simple sorting is being done by everyone, just select the data, go to **Data** tab and click on **Filter** (grey funnel looking icon) under the **Sort & Filter** section. You will now have drop-down arrows next to the column headers of each column. You can simply click on the arrows and select ascending or descending.

Â

But this ascending or descending sorting can be done based on only any 1 column.

Suppose you have data with a lost of contacts, wherein you have the person’s State and Country as columns.

By the above method you can simply sort as per Either State or Country. But now you want to sort Country-wise — State-wise! How do you do this sorting on multiple columns?!

Â

Very simple!

Select all data. go to the **Data **tab on the ribbon menu, under the** Sort & Filter** section, select **Sort**.

A window will open.

Under the **Sort By**, select the Country column, then click on **Add Level. **Under the **Sort By**, select the State column. Click OK.

Â

You’re done! ðŸ™‚