Showing posts with label EXCEL TIP. Show all posts
Showing posts with label EXCEL TIP. Show all posts

10 Essential Microsoft Excel tips

January 09, 2025 0
In this block, we are going to learn about the 10 essential Excel tips which will be very helpful for your excel work and it worked faster smoother and very efficient way to do the work on Microsoft Excel.

1. KEYBOARD SHORTCUTS 

CTRL + V: For Paste 
CTRL + C: FOR Copy 
CTRL + Z: For undo your last action 
CTRL + Arrow Keys: For navigate each of the data ranges from bottom to up and from left to right 
CTRL + shift + L: For applying or removing the filters 

2. AUTO FILL

Auto fill used for the data which will filled automatically. If you write Monday, then Tuesday will be automatically filled in the next row. If you fill 1 will fill 2 in the next row automatically. it will fill the series of data in just one click. 




3. BASIC FORMULAS 

Formulas like 
SUM(A1:A10): Will give the sum of all the data from A1 cell to A10 cell
AVERAGE(A1:A10): Will give you avarage of the dat filled in cell A1 to A10 
VLOOKUP(A1,VALUE, TABLE,COLUMN,[RANGE]): will search for the specific value from the table 
IF(A1>100,"YES","NO"): It is the condition if the value of the cell A1 is higher than 100 will return YES otherwise it will return NO 
Understanding these formulas will give you strong foundation

4. CONDITIONAL FORMATTING 

Conditional formatting is used for visualizing and highlighting your data as per the condition you apply
Home>Style>Conditional Formatting


5. FREEZE PANES 

Which will freeze the first row or the first column. If you are working with a large data set, you need to see the what is in the first column and first row. These will have very very helpful for the large database. 
You can access it from View>Freeze Panes 


6. FILTER 

Filter will help you to filter out the specific data. It will sort and view specific data. If you have a large data set and you want to see the specific data this will help you. It is very easily to find out the specific data
Home>Editing>Sort & Filter



7. EXCEL TABLES 

Excel tables will formatting your table automatically, and making it filtering or sorting easily and making formulas for dynamic ranges. Will use for working with frequently updated data. To convert your data in to table use CTRL + T 



8. PROTECT YOUR WORKSHEET 

After making your worksheet. You don’t allow other people to see or to edit the sheet. You can protect your sheet by using a password 
Review>Protect sheet 



9. REMOVE DUPLICATES 

Remove duplicate means you have a bunch of data large data and you want to delete some specific duplicate entry or duplicate values. You can use remove duplicate It will remove the duplicate data and clean up the data 
Data>Remove Duplicates 

10. SAVE TIME WITH TAMPLATES 

In Microsoft excel some of the built in free templets are available about calendar, budget, financial etc. It will save your time and customization is also available 
File>New 



We hope this post will help you to understand and expand your excel knowledge By mastering this 10 Excel tips, you become very confident and can do work very efficiently and fast
Tags # EXCEL TIP Continue Reading
No comments:
Labels:

Conditional formatting in EXCEL

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?

  1. Conditional formatting highlights the specific data as per condition  applied by following rules and values
  2. you can quickly visualise the data you want as per condition given in different colors
  3. by using the conditional formatting you can save your precious time because your hefty work is done by a simple condition based formatting 
  4. 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
No comments:
Labels:

How to Use Freez and Unfreez in Excel

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
No comments:
Labels:

Hide-Unhide Column and Rows

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
No comments:
Labels:

How to add Drop Down list in excel

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.
Example


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

Example

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.

Example

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

Example

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

Example

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

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.

Example


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.

Example



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
No comments:
Labels:

Convert Numbers into Words

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
No comments:
Labels:

How To Use Mail Merge | Microsoft Excel | Microsoft Word

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
No comments:
Labels: