Information
AI Chat

Excel Tips and Tricks

Helps in Learning Short Cut Keys in Excel

Course

Advanced Excel (Excel)

8 Documents

Students shared 8 documents in this course

University

Siva Sivani Institute of Management

Academic year: 2022/2023

Listed bookPsicometría

Uploaded by:

Anonymous Student

This document has been uploaded by a student, just like you, who decided to remain anonymous.

Siva Sivani Institute of Management

Comments

Please sign in or register to post comments.

Preview text

Email: ramzanrajani@gmail | Web: UpGradeSkill 1

MS Excel

Tips & Tricks

Ramzan Rajani’s Favourite

2022

MS Excel Tips & Tricks

Ramzan Rajani Microsoft Excel Specialist Microsoft Certified Trainer

Contents

Ramzan Rajani – Email: ramzanrajani@yahoo – Web: ramzanrajani
- 1. Quickly Insert System date / time
- 1. Combining first & last name / product ID & product code
- 1. Convert your Horizontal data into Vertical
- 1. Highlight duplicate records
- 1. Removing duplicate records
- 1. Split first name and last name into separate cells
- 1. Change Case of Text in Excel
- 1. Protect worksheet - Protecting your data from modifying
- 1. Protect workbook - Protecting your sheet from deletion
- 1. Protect your excel file with password while opening
- 1. Pin Documents, Workbooks to the Recent File List in Excel
- 1. Compare 2 Excel Files using View side by side Mode
- 1. Working with several Excel files everyday? Use workspace feature
- 1. Use Text Format to Preserve Leading Zeros in Excel
- 1. Use fixed number of zeros in the starting of number
- 1. Shortcuts (Select/Insert/Delete/Hide& Unhide rows, cols & sheets)
- 1. Quick Filtering
- 1. Creating drop down in cells for Region / Country / City orTeam
- 1. Shortcuts (Alignments / Borders / Sorting /Filtering / Hide/ Unhide Ribbons)
- 1. Removing extra spaces for the sentence (TRIM)

Ramzan Rajani – Email: ramzanrajani@yahoo – Web: ramzanrajani 4

2) Combining first & last name / product ID & product code

Above formula can be written in this way also =Concatenate(B2," ",C2)

Email: ramzanrajani@gmail | Web: UpGradeSkill 5

3) Convert your Horizontal data into Vertical

Steps

Select A1 to P
Press - Ctrl C - To copy the data
Select Cell A
Press Ctrl Alt V (Shortcut of Paste Special)
Select Transpose → OK

4) Highlight duplicate records

Want to find duplicate or unique entries in Excel 2010?

Email: ramzanrajani@gmail | Web: UpGradeSkill 7

Steps:

Keep your cursor on any cell inside the data or select whole data.
Data Ribbon → Remove Duplicates (As shown in the above screenshot)
Unselect All
1. Select Emp ID column and click on OK

6) Split first name and last name into separate cells

Use the Convert Text to Columns Wizard in Excel when you need to split combined data into separate columns, such as a first name and last name; or city, state, and zip code. This type of combined data often results when you open or import files created in another application.

Ramzan Rajani – Email: ramzanrajani@yahoo – Web: ramzanrajani 8

If necessary, insert blank columns to the right of the cells you want to convert into multiple columns you want your data in three columns, you must have two blank columns

Steps: ➢ Select Column B ➢ Data Ribbon --> Text to column ➢ Select Delimited --> Next ➢ Select Space --> Finish

7) Change Case of Text in Excel

One question I get all the time is how to change the case of text in Excel. Some applications require case-specific text to make a match with its data. Or you may just want to clean up

Ramzan Rajani – Email: ramzanrajani@yahoo – Web: ramzanrajani 10

To prevent a user from accidentally or deliberately changing, moving, or deleting important data from a worksheet or workbook, you can protect certain worksheet or workbook elements, with or without a password

Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.). On the Review tab, in the Changes group, click Protect Sheet.

8) Protect worksheet - Protecting your data from modifying

Email: ramzanrajani@gmail | Web: UpGradeSkill 11

You can lock the structure of a workbook, which prevents users from adding or deleting worksheets or from displaying hidden worksheets. You can also prevent users from changing the size or position of worksheet windows. Workbook structure and window protection applies to the whole workbook.

On the Review tab, in the Changes group, click Protect Workbook.

10) Protect your excel file with password while opening

By using the protection you can prevent user from opening your file

To protect your Excel 2010 spreadsheet, use the following steps.

➢ In an open spreadsheet, click the File tab. The Backstage view opens. ➢ In the Backstage view, click Info.

Email: ramzanrajani@gmail | Web: UpGradeSkill 13

Steps in Excel 2010:

➔ Select the File menu then select Recent and you can see the recently used files & folders. When you click a pin in either list, it turns blue AND moves to the top of the list.

12) Compare 2 Excel Files using View side by side Mode

Many times we have 2 or more workbooks with same column heading but different data. We want to compare both and see how they differ. Here you can you View side by side mode in Excel.

Ramzan Rajani – Email: ramzanrajani@yahoo – Web: ramzanrajani 14

If you enable Synchronous Scrolling both the documents will scroll together.

13) Working with several Excel files everyday? Use workspace feature

If you work with multiple Excel workbooks every day, and have to open all the Excel files daily to work on them, then here is a handy tip. 1. Open all the files you frequently use together. 2. Arrange them (Horizontally / Vertically) 3. Go to View ribbon and click on Save Workspace (Save on Desktop) 4. Give a name to your workspace 5. Now, whenever you need to open all these files together, just double click on workspace file which you have saved. 6. You are done!

Ramzan Rajani – Email: ramzanrajani@yahoo – Web: ramzanrajani 16

→ Select the cells where you are going to enter these numbers, from Home Ribbon > Number area, select Text as cell type. Excel will now treat any value you enter as Text , not as number. So when you type 0072 , it will appear as 0072 , exactly same.

Calculation will not work as numbers are converted into the text

15) Use fixed number of zeros in the starting of number

If you want the number to show up in 6 digits (with leading 0s if needed), you can use the cell format code 000000

Email: ramzanrajani@gmail | Web: UpGradeSkill 17

Steps: Select the cells in which you have numbers and press Ctrl 1 From Number tab choose Custom Enter the format code as 000000

16) Shortcuts (Select/Insert/Delete/Hide& Unhide rows, cols & sheets)

Sr. Action Shortcut 1 Select Row Shift Space

Email: ramzanrajani@gmail | Web: UpGradeSkill 19

➔ Select the cell in which you wish to filter. ➔ Right click on that cell → Filter → Filter by Selected Cell’s value

Ramzan Rajani – Email: ramzanrajani@yahoo – Web: ramzanrajani 20

18) Creating drop down in cells for Region / Country / City orTeam

Steps:

Select the cells where you wish to create the Drop downs.
Select Data Ribbon → Data Validation → Settings → In Allow -- Select List → In Source either type Region names separated by commas or select the range in which details are already typed.

Note: If its excel 2010 and above then we can also select the drop down list data from another sheet, else we have to create the named range if it's lower version. After creating name range of the data, in Allow List, Source will be =region

Was this document helpful?