Microsoft Excel is one of the most used software applications of all time. Excel is Most Suitable Spread Sheet Application Software For entering all sorts of data and performs financial, mathematical or statistical calculations. Microsoft Excel usually comes bundled with Microsoft Office and is compatible with other applications offered in the suite of products. Microsoft Excel usually comes bundled with Microsoft Office and is compatible with other applications offered in the suite of products.

Ribbon

Customize the Ribbon

You can easily create your own tab and add commands to it.

1. Right-click anywhere on the ribbon, and then click Customize the Ribbon.

2. Click New Tab.

3. Add the commands you like.

4. Rename the tab and group.

You can collapse the ribbon to get extra space on the screen. Right-click anywhere on the ribbon, and then click Collapse the Ribbon (or press CTRL + F1).

Workbook

A workbook is another word for your Excel file. When you start Excel, click Blank workbook to create an Excel workbook from scratch.

Open an Existing Workbook

To open a workbook you've created in the past, execute the following steps.

1. On the File tab, click Open.

2. Recent shows you a list of your recently used workbooks. You can quickly open a workbook from here.

3. Click Browse to open a workbook that is not on the list.

Close a Workbook

To close a workbook (and Excel), click the upper right X. If you have multiple workbooks open, clicking the upper right X closes the active workbook.

Create a New Workbook

Sometimes you want to start all over again. To create a new workbook, execute the following steps.

1. On the File tab, click New.

2. Click Blank workbook.

Turn off the Start screen

When you start Excel, it shows a start screen that lists recently used Excel files and templates. To skip the start screen and always start with a blank workbook, execute the following steps.

1. On the File tab, click Options.

2. Uncheck 'Show the Start screen when this application starts'.

3. Click OK.

Worksheets

A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

Select a Worksheet

When you open an Excel workbook, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of the document window.

Insert a Worksheet

You can insert as many worksheets as you want. To quickly insert a new worksheet, click the plus sign at the bottom of the document window.

Rename a Worksheet

To give a worksheet a more specific name, execute the following steps.

1. Right click on the sheet tab of Sheet1.

2. Choose Rename.

Move a Worksheet

To move a worksheet, click on the sheet tab of the worksheet you want to move and drag it into the new position.

Delete a Worksheet

To delete a worksheet, right click on a sheet tab and choose Delete.

Copy a Worksheet

You can recreate the worksheet, but this is time-consuming. It's a lot easier to copy the entire worksheet and only change the numbers.

1. Right click on the sheet tab

2. Choose Move or Copy.

The 'Move or Copy' dialog box appears.

3. Select (move to end) and check, Create a copy.

4. Click OK.

Format Cells

When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or another formatting (alignment, font, border, etc).

1. Enter the value 0.8 into cell B2.

By default, Excel uses the General format (no specific number format) for numbers. To apply a number format, use the 'Format Cells' dialog box.

2. Select cell B2.

3. Right-click, and then click Format Cells (or press CTRL + 1).

The 'Format Cells' dialog box appears.

4. For example, select Currency.

5. Click OK.

Cell B2 still contains the number 0.8. We only changed the appearance of this number. The most frequently used formatting commands are available on the Home tab.

6. On the Home tab, in the Number group, click the percentage symbol to apply a Percentage format.

7. On the Home tab, in the Alignment group, center the number.

8. On the Home tab, in the Font group, add outside borders and change the font color to blue.

Find & Select

You can use Excel's Find and Replace feature to quickly find specific text and replace it with other text. You can use Excel's Go To Special feature to quickly select all cells with formulas, comments, conditional formatting, constants, data validation, etc.

Find

To quickly find specific text, execute the following steps.

1. On the Home tab, in the Editing group, click Find & Select.

2. Click Find.

The 'Find and Replace' dialog box appears.

3. Type the text you want to find. For example, type Ferrari.

4. Click 'Find Next'.

5. Click 'Find Next' to select the second occurrence.

6. To get a list of all the occurrences, click 'Find All'.

Replace

To quickly find specific text and replace it with other text, execute the following steps.

1. On the Home tab, in the Editing group, click Find & Select.

2. Click Replace.

3. Type the text you want to find (Veneno) and replace it with (Diablo).

4. Click 'Find Next'.

5. Click 'Replace' to make a single replacement.

Go To Special

You can use Excel's Go To Special feature to quickly select all cells with formulas, comments, conditional formatting, constants, data validation, etc. For example, to select all cells with formulas, execute the following steps.

1. Select a single cell.

2. On the Home tab, in the Editing group, click Find & Select.

3. Click Go To Special.

4. Select Formulas and click OK.

Templates

Existing Templates

To create a workbook based on an existing template, execute the following steps.

1. On the File tab, click New.

2. Use the search box to search for online templates or click a template from one of the featured templates.

3. Click Create to download the template.

Create a Template

If you create your own template, you can safely store it in the Templates folder. As a result, you can create new workbooks based on this template without worrying that you overwrite the original file.

To create a template, execute the following steps.

1. Create a workbook.

2. On the File tab, click Save As.

3. Click Browse.

4. Enter a file name.

5. Select Excel Template (*.xltx) from the drop-down list.

6. Click Save.

To create a workbook based on this template, execute the following steps.

7. On the File tab, click New.

8. Click a Template.

Data Validation

Data Validation Example

In this example, we restrict users to enter a whole number between 0 and 10.

Create Data Validation Rule

To create the data validation rule, execute the following steps.

1. Select cell C2.

2. On the Data tab, in the Data Tools group, click Data Validation.

On the Settings tab:

3. In the Allow list, click Whole number.

4. In the Data list, click between.

5. Enter the Minimum and Maximum values.

Input Message

Input messages appear when the user selects the cell and tell the user what to enter.

On the Input Message tab:

1. Check 'Show input message when the cell is selected'.

2. Enter a title.

3. Enter an input message.

Error Alert

If users ignore the input message and enter a number that is not valid, you can show them an error alert.

On the Error Alert tab:

1. Check 'Show error alert after invalid data is entered'.

2. Enter a title.

3. Enter an error message.

4. Click OK.

Keyboard Shortcuts

Keyboard shortcuts allow you to do things with your keyboard instead of your mouse to increase your speed.

Basic

Select cell B2.

1. To select the entire range, press CTRL + A (if you press CTRL + a one more time Excel selects the entire sheet).

2. To copy the range, press CTRL + c (to cut a range, press CTRL + x).

3. Select cell A6 and press CTRL + v to paste this range.

4. To undo this operation, press CTRL + z

Moving

Select cell B2.

1. To quickly move to the bottom of the range, hold down CTRL and press ↓

2. To quickly move to the right of the range, hold down CTRL and press →

Try it yourself. Hold down CTRL and press the arrow keys to move from edge to edge.

Selecting

Select cell A1.

1. To select cells while moving down, hold down SHIFT and press ↓ a few times.

2. To select cells while moving to the right, hold down SHIFT and press → a few times.

Formulas

Select cell F2.

1. To quickly insert the SUM function, press ATL + =, and press Enter.

2. Select cell F2, hold down SHIFT and press ↓ two times.

3. To fill a formula down, press CTRL + d (down).

Note: in a similar way, you can fill a formula right by pressing CTRL + r (right).

Formatting

Select the range B2: F4.

1. To launch the 'Format Cells' dialog box, press CTRL + 1

2. Press TAB and press ↓ two times to select the Currency format.

3. Press TAB and press ↓ two times to set the number of decimal places to 0.

4. Press Enter.

Print

Print a Worksheet

To print a worksheet in Excel, execute the following steps.

1. On the File tab, click Print.

2. To preview the other pages that will be printed, click 'Next Page' or 'Previous Page' at the bottom of the window.

3. To print the worksheet, click the big Print button.

What to Print

Instead of printing the entire worksheet, you can also only print the current selection.

1. First, select the range of cells you want to print.

2. Next, under Settings, select Print Selection.

3. To print the selection, click the big Print button.

Multiple Copies

To print multiple copies, execute the following steps.

1. Use the arrows next to the Copies box.

2. If one copy contains multiple pages, you can switch between Collated and Uncollated. For example, if you print 6 copies, Collated prints the entire first copy, then the entire second copy, etc. Uncollated prints 6 copies of page 1, 6 copies of page 2, etc.

Orientation

You can switch between Portrait Orientation (more rows but fewer columns) and Landscape Orientation (more columns but fewer rows).

Page Margins

To adjust the page margins, execute the following steps.

1. Select one of the predefined margins (Normal, Wide or Narrow) from the Margins drop-down list.

2. Or click the 'Show Margins' icon at the bottom right of the window. Now you can drag the lines for change manually page margins.

Scaling

If you want to fit more data on one page, you can fit the sheet on one page. To achieve this, execute the following steps.

1. Select 'Fit Sheet on One Page' from the Scaling drop-down list.

Paste

Most of the time, you'll simply need to paste static Excel data in a Word document.

1. Select the Excel data.

2. Right-click, and then click Copy (or press CTRL + c).

3. Open a Word document.

4. Press CTRL + v.

5. Click the icon in the upper left corner of the table and add borders.

Paste Link

You can also link the source data in Excel with the destination data in Word. If you change the data in Excel, the data in Word is updated automatically.

1. Repeat steps 1 to 3 above.

2. In Word, on the Home tab, in the Clipboard group, click Paste.

The Paste Special dialog box appears.

4. Click Paste link, HTML Format.

5. Click OK.

6. Click the icon in the upper left corner of the table and add borders.

7. Change the Excel data.

Protect

Encrypt an Excel file with a password so that it requires a password to open it.

1. Open a workbook.

2. On the File tab, click Save As.

3. Click Browse.

4. Click on the Tools button and click General Options.

5. In the Password to open box, enter a password and click OK.

6. Reenter the password and click OK.

7. Enter a file name and click Save.

Functions

SUM

SUM Function Use for Calculate Numbers Like.

1+1 = 2

2-1 = 1

=sum(Cell Address2-Cell Address1)

MIN

MIN Function Use for Find The Minimum Value Of Numbers/Number Range Like.

1,2,3,4,5

Minimum = 1

=Min(Cell Range)

MAX

MAX Function Use for Find The Maximum Value Of Numbers/Number Range Like.

1,2,3,4,5

Minimum = 5

=Max(Cell Range)

AVERAGE

AVERAGE Function Use for Find The Average of Numbers/Number Range Like.

1,2,3,4,5

Average = (1+2+3+4+5+)/5

=Total/Count

=3

=Average(Cell Range)

COUNT

COUNT Function Use for Count Numbers Like.

1,2,3,4,5

Count = 5

=count(Cell Range)

* Can't Count Letters/Words

COUNTA

COUNTA Function Use for Count Numbers and Words Letters Like.

1,2,3,4,5,a,b,c

Count = 8

=counta(Cell Range)

ROUND

ROUND Function Use for Round Numbers Like.

2.456

=Round(Cell Address,0)

Round =2

=Round(Cell Address,1)

Round =2.5

=Round(Cell Address,2)

Round =2.46

SUMIF

SUMIF Use For getting Sum With A Criteria.

As An Example, You Need To Get The Sum of Number List Which Are Bigger Than Average.

Guess Number list As 1,2,3,4,5

Average Will Be 3

The Function For This Will Be Like This.

=sumif(Numbers Cell Range,Criteria)

=sumif(Cell Range,">3")

COUNTIF

COUNTIF Use For getting Count With A Criteria.

As An Example, You Need To Get The Count of Number List Which Are Bigger Than Average.