Excel Trivia | Everything about Excel Function and Formulas: EXCEL TIP

Showing posts with label EXCEL TIP. Show all posts

Conditional formatting in EXCEL

Urvil Darji December 16, 2024 0

Conditional formatting is powerful functions in Microsoft excel. it is underutilized function in Microsoft excel.it converts raw data in to manageable data which visualized by different colors and icons. For Example lowest and highest performance area of company which is highlight by simply using conditional formatting. Conditional formatting make your work easy and eye catching wherever data needs

WHAT IS CONDITIONAL FORMATTING??

Conditional formatting is automatic function which highlight the conditional values or rules in different color, icon, font style etc.Manually formatting of data will make errors and other problems while applying conditional formatting makes our work too much easy.

WHY USE CONDITIONAL FORMATTING?

Conditional formatting highlights the specific data as per condition applied by following rules and values
you can quickly visualise the data you want as per condition given in different colors
by using the conditional formatting you can save your precious time because your hefty work is done by a simple condition based formatting
Conditional formatting is more accurate and precise

HOW TO APPLY CONDITIONAL FORMATTING

1. Select Your Data Range
Highlight the cell where you want to apply formatting

2. Go To Conditional Formating
on the home tab locate Conditional Formatting button in the style group

3. Choose The Rule Type

HIghlight the cell rule: Highlight the values greater than less than or equal to a specified value.

Highlight the cell rule: Highlight the values greater than less than or equal to a specified value.
Top/Bottom Rules: Highlight the top 10 items, bottom 10 items

Data Bars, color scale, icon sets: use visual aids to represent data trends
Custom Formulas: create advanced rules using excel formulas

4. Set Formatting Options
Define the colors, fonts, or borders for the highlighted cells.

5. Clock Ok
Your selected cell will now reflect the conditional formatting based on your rules

Examples

For a student how much students get more than 80 marks from allt he students than
We have to use conditional formatting

We have this data and we have to find students with 80 or more than 80 marks

Than we use conditional formatting

Step 1 Select the range

Step 2 Click on Conditional Formatting from the Home Tab Style Section

Step 3 Select Highlight Cells Rules than Select Greater than and write 80 and Select color combination you want to highlight

Here We can see that the every students who has more than 80 marks are highlighted

Advanced Tip:

For greater flexibility you can use formulas to define conditional formatting by using New Rules

For that you have to

select the range of data

Got o Conditional Formatting=>New Rules=>Use a formula to determine which cell to format

Enter the formula

Choose your desired formatting

Common pitfalls to avoid

Applying rules to wrong range
Always double check your your selected range to avoid unexpected results

Too many rules:
Overloading your sheet with multiple rules can slow down performance
Prioritize clearly

Not understanding formula based rules:
Formula needs to start with equal to (=) sign and reference the correct cell

Tags # EXCEL TIP Continue Reading

How to Use Freez and Unfreez in Excel

Urvil Darji July 23, 2018 0

You have lots of data and working on it, you want the first row or the first column of your sheet should be watchable even if you are working on the last cell of your data.

This type of the difficulties is faced by everyone who is working with lots of data.

The solution is to freeze the column or row, where your parameters existed. You can also unfreeze the column and row when your work finished.

"Freeze Panes option is helpful When you are dealing with lots of data"

Step 1: To select the Rows that you want to Freeze

Here we want to freeze the first two columns so we have to select the first three rows of the sheet, in which the first two rows will be freeze.

Step 2: Go to the view option and press Freeze Panes and Select Freeze Panes

After Selecting the first three rows in which we want to Freeze first two rows, click on the View button on the top side of the sheet and search Freeze panes option. Click on freeze panes.

Step 3: The first two rows have Freeze

Now scroll down the sheet you can see the first two rows are freeze and the rows below are scrolling.

To Unfreeze

Step 4: Now select the Freeze Rows and click on Unfreeze Panes.

It is a reverse process of freeze panes. You have to select the rows you have freeze and click on the view button and then click on Freeze Panes and select Unfreeze Panes. It will unfreeze the first two rows that we have freeze.

Note:

Here you can Freeze Panes Horizontally and Vertically.

If you want to freeze column and row in the middle of the sheet you can do it by selecting the single row.

Freeze pane is also called a lock.

How would you like this content to write us in the comment box!

If you face any difficulty in the content feel free to ask!

Thank You.

Tags # EXCEL TIP Continue Reading

Hide-Unhide Column and Rows

Urvil Darji July 06, 2018 0

You can hide any column or row by using hide Command. And you can unhide the same column and row by using unhide command.

You can hide a specific column or row and undie all hide column and row same time.

It is a very useful command when there are lots of columns and you want to work on some two or three columns at that time you can hide the unwanted column.

Instead of deleting Column and Row, To hide would be a better Choice.

Step 1: To select Columns that you want to hide.

Here we want a name and mobile no of the person. Names are in "C" Column and Mobile no is in "H" Column. If the column between the "C" and "H" are hide than we can easily access our data. Here we selected from "D" Column to "G" Column.

Step 2: Now press right click and select hide button from the list.

Here we have Selected fromColumn "D" to column "G" and press right click. So we can see a hide button to second last. Click on hide button to hide the selected column.

Here we can see Column "D" to "G" are hidden and We can easily access name and mobile no of the person.

Step 3: Now we unhide the columns. Select the Column "C" and Column "H" and press right click.

Here we have Selected Column "C and "H" and then we can see the option unhide option in the last.

Step 4: Press Unhide and see you can see you are previously hiding column again.

This is our original data after using unhide.

How would you like this content to write us in the comment box!

If you face any difficulty in the content feel free to ask!

Thank You.

Tags # EXCEL TIP Continue Reading

How to add Drop Down list in excel

Urvil Darji July 04, 2018 0

Drop down menu is used to select a parameter from listed objects. Drop down menu is mainly used in forms.

By using the dropdown list you just have to select the item instead of writing it every time. It speeds up your work also.

Drop down will increase your data entry speed.

Let's see how to aad drop-down list in excel cell

Follow the steps

Step 1: Open the Microsoft Excel.

Step 2: Enter the parameters which you want to appear in the list of drop down. So we have entered the name of days here.

Do not place any blank cell on the list.

Step 3: Now click on the first cell of your parameters. And then press right click. Then select "Name a Range".

Step 4: A Window will open when you click on the name a range option.

You have to write the name of your parameter range. Here we are using days. Then click on "OK". Now you can hide or protect the column in which you have written your data from the worksheet.

Step 5: Now Click on the cell where you want to add drop-down list.

Step 6: Now Click on the Data tab on the menu bar and select Data Validation Option.

Step 7: From the data validation window, in the setting tab Select List in Validation Criteria Allow.

Step 8: In the source, option Select the range of your parameters which you want to show in your drop-down list.

In Source it will show the name that you gave at the "Name a Range" Option. Here we have given Days So we can see the name of our source is Days.

Step 8: Input Message: If you want to show any message when a pointer roaming to the cell of the drop-down list then you can write a message in the box otherwise it is not going to effect on the result.

Step 9: Error Alert: If any invalid data has been entered then this function shows error.

Step 10: Click on "OK". Your drop down list is ready to use. You click on the down arrow and it will show you all the value and select any of one.

Please Share your views in the Comment box.
If you need any help with excel Write in the comment box.

Thank You

Tags # EXCEL TIP Continue Reading

Convert Numbers into Words

Urvil Darji July 01, 2018 0

We always want our numerical value can automatically convert into Spelling would be a great thing. We must have to make a calculation and write a spelling of the same in billing, balance sheets etc.

Now you can convert a number into spelling by following these steps. It is so much easy.

STEP 1: Start Microsoft Excel.

STEP 2: Press Alt+F11. It will open the Microsoft Visual Basic program screen.

STEP3: Click on the Insert menu and Select Module.

STEP 4: A module Window will open. You have to copy a code in this window. Click here for Code

STEP 5: Click on the file and Select Close and Return To Microsoft Excel.

STEP 6: Now its time to use the function SpellNumber. There are two methods to use SpellNumber

SpellNumber By using Reference cell: Reference cell which you want to convert into words writes like

=SpellNumber(B2)

Where B2 is your reference cell which you want to convert into words

SpellNumber By Manually Entry: if you want to convert any number in words you have to write it like

=spellNumber(50.60)

It will return you “Fifty Dollars and Sixty Cents”

Now Save macros for the excel sheet. Click on save as and select Excel Macro-Enabled Workbook. Which save your macro also

Note: This function only works for your current file in which you have to add a macro for it.

You can also change Dollars into rupees and cent into Paise by using Replace function in Visual Basic window.

How would you like this content to write us in the comment box!

If you face any difficulty in the content feel free to ask!

Thank You.

Tags # EXCEL TIP Continue Reading

How To Use Mail Merge | Microsoft Excel | Microsoft Word

Urvil Darji March 08, 2018 0

Hello Friends,

In today's fast working environment we want short tricks to complete our work in less time. Everyone has listened about mail merge bur more of that don't know how to use and what benefits are.

By using mail merge you can send a letter, notice, invitation etc in just 5 minutes. you just have to create excel sheet and a letter or notice whatever you want to send.

Mail merge is a function of Microsoft Word. we importing details from excel.

There are three main parts to create a Mail Merge, (1) Creating Contacts (2)import contacts to word ( 3) Use Mail Merge.

We are making an invitation card for that we need a list of our guests and address to send.

(1) Creating a contact list: We are taking 10 contacts with their Name Address and Mobile no.

Data For Mail Merge

And Save Excel File.

(2) Importing Contracts to Word

Step 1: Open Microsoft Office Word, Take New Page. Click on Mailings(Top of the Microsoft Windows) It will show toolbar

Select Mailings For Mail Merge

Step 2: Now Click on Select Recipients(In the Start Mail Merge Section), Then click on use Existing List(Which We have created in Excel)

A new Window will open. We use our list that we created in part 1 to select the excel file from the Select Data Source window.

(3) Use Mail Merge

Step 1: Go to the place where you want to insert contact information. Click on Insert Merge Field (Will show your data that you entered in excel file)

Merge Fileds Inserted for Mail Merge

When inserting all the field its look like the above image.

Then click on the Preview Result will show you a letter or invitation with contact information for all the contact by clicking next and previous.

Then click on finish and merge. Will Show you three option

(1)Edit Individual Document: Will open each recipients document and allow you to modify (2)Print Document: Will print all recipients document

(3) Send E-Mail Messages: Which sends a document to each recipient's e-mail.

Follow the instruction on the page you visit.

We have added a link of word file and excel file.

How would you like this content to write us in the comment box!

If you face any difficulty in the content feel free to ask!

Thank You.

Tags # EXCEL TIP Continue Reading

How to convert Pdf file to Word | Microsoft Word

Urvil Darji February 22, 2018 0

Hello Friends,
Sochiye aapke pass PDF file he jo aapko bahut upyogi he or aapko us PDF me kuchh changes krne he, ab yahi file aapko WORD format me chahiye.
hum yaha pe 2 tarike se PDF file ko Word me convert krenge, Google Docs or Microsoft Ofiice 2016 ko use krke

1 Using Google DOCs

Step1: Aaapki jo PDF format ki file he usko google drive me upload krna he.Google drive me sign in kar na he. usme my drive ke down aero pe click krne se drop down list khulega. usme se aapko upload files select krnahe.

Step 2:Uplod files pe click krne ka bad aapke PC ki screen pe ek window khulegi. Jisme aap ko aapki PDF file select krni he, jise aap ko word me chahiye. Humne yaha pe sample file select kri he aur file select krne k bad aap ko opne pe click kran he.

Step 3: Ab aapki sample PDF file google drive me dikhegi. ab aapki PDF vali file pe right click krna he. USe Open WIth pe jana he aur google docs pe click krna he.

Step 4:Ab ek nayi tab khule gi jisme aapki file dikhegi same as PDF file but word format me. ab aapko file pe click krnahe or usme se aapko download as pe click krne se ek side bar open hoga jisme se aapko Microsoft Word wala option select krna he. jisjse aapki PDF file Word me convert ho jayegi.

Yaha pe aapki word file me thoda specing un balanced ho jayega jo thik krana hoga.

pdf file se jab word me convert krte time pe pdf ka decoding word me encode hota he or isiliye hume kuchh jyada specings manage krni pdti he or jo pdf me table hota he wo Word me encode nahi hota he.

Agar Koi Photo PDF file me hoga or usko Word me convert krenge to wo word me encode nahi hoga.

2 Using Microsoft Office 2016

Hamne upar dekha k hamara PDF file Word me conver ho gya pr specoings ki problems or images ki problems rheti he. Microsoft Office 2016 ko agar use krenge to hamari yew problem bhi solve ho jayegi.

Ye bahot hi simple tarike se aap ki PDF file ko Word me convert kr dega or without kisi problem ke.

Step 1: To huma yaha pr Microsoft Office 2016 open krte he or is me Blank Document Select krte he.

Step 2:ab Files me OPEN fir Browse or aapko jo file convert krni he use select kijiye.(Mene yaha pe flight ki ticket select kri he) then open pe click kijiye.