Category: MS Excel Formulas

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…

Use data validation in Excel to make sure that users enter certain values into a cell. 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…

Instead of creating an Excel workbook from scratch, you can create a workbook based on a template. There are many free templates available, waiting to be used. 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. Excel creates the workbook Academic calendar (any year)1.xlsx based on this template. Create a Template If you…

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’. Excel selects the first…

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…

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

The tabs on the ribbon are File, Home, Insert, Page Layout, Formulas, Data, Review, and View. Excel selects the ribbon’s Home tab when you open a workbook. 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. Note: you can also add new groups to existing tabs. To hide a tab, uncheck the corresponding checkbox. Click Reset, Reset all customizations, to…

A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel. For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1. For example, cell A3 below contains the SUM function which calculates the sum of the range A1:A2. Enter a Formula To enter a formula, execute the following steps. 1. Select a cell. 2. To let Excel know that you want to enter a formula, type an equal sign (=). 3. For example,…