Introduction
We deal with a lot of data every day. There are many sources from which data can be collected, like websites, and tools. We store these data, clean them, work on them as required, and use them for decision-making. To perform these activities we need tools to store data. Microsoft Excel is one of the easiest to learn and easily available tools. in this Excel tutorial article, we will cover a few excel tips and tricks.
The MS excel tool is very useful for personal and business use. It has multiple functions, formulas, and uses cases to store our data in an organized way. using this Excel tutorial we will try to cover most of the Excel Tips and Tricks.
Why Excel?
Excel is a spreadsheet program, provided by Microsoft. It can be used for both personal as well as professional reasons. It is used by almost all the departments in an organization for many activities starting from data entry work to preparing different reports from presenting data visually in charts, graphs, and tables to automating daily activities which would take less time than manually doing it.
How to get Microsoft Excel Installation file?
Excel can be purchased from any computer shop that sells software. It is part of Microsoft Office. Or you can go to the Microsoft website and download Microsoft Excel.
How to open Excel file?
Go to the start menu, and type excel. Click on the MS Excel icon. It will open excel. Once open you get the below page.
Alternatively, Press the Windows key + R on your keyboard to open the Run command. Type the “excel” command and press enter.
Below are the basic details of the Microsoft excel spreadsheet.
What is a Ribbon in Excel?
Ribbon contains various Menu options in Excel. Each Menu has a set of commands available in Excel to perform operations. For Example,
To open the file: Go to File Menu > Open > Select file path > Click on filename > Click on Open (Ctrl+O)
How to start excel in safe mode?
To start Excel in safe mode, follow the steps below:
you can start Excel in safe mode using the Run command.
1. Press the Windows key + R on your keyboard to open the Run command.
2. Type “excel.exe /safe” or “excel.exe /s” in the Run dialog box and press Enter.
3. Excel will start in safe mode.
What is a Formula bar in Excel?
It shows the content of a cell. (Text, number, characters, formulas)
We can do many mathematical calculations in excel. There are different formulas available for this. A formula in excel starts with the “=” sign as mentioned in the example below.
What is a Name Box in Excel?
Shows the address of the selected cell
You can also go to a specific cell by entering the Cell Address in the Name Box.
For Example Type A1 in the Name box. It will highlight A1 Cell
What are Sheets in MS Excel?
By default, there are 3 Sheets available in one Excel file. You can add a new sheet using Insert Worksheet (Shift+F11) button next to the default sheets. click on any Sheet name to select that specific spreadsheet.
How to create a formula in Excel?
There are different ways:
- Select the cell where you want to add the formula.
- You can type the formula in Formula Bar as shown below OR
- You can select the cell and type “=35+70”. Then hit enter OR
- You can put the number 35 in cell A1, 70 in B1
- Then in cell C3 type “=”
- Select cell A1
- Add + Sign
- Select cell B1
- Hit enter
How to select Range in Excel?
Range includes
• Selection of 1 cell: By clicking on any single cell
• Selection of multiple cells: Select the first cell, keep the left key of the mouse pressed, and drag till the last cell of the range. For example select range A1: B10
• Selecting entire column: By clicking on a column header (Ctrl + SpaceBar), below example: Column H
• Selecting entire row: By clicking on a row number (Shift + SpaceBar), below example: Row 15
• Selection of the entire sheet: Click on the triangle button shown in the image below to select all cells from the spreadsheet.
How to Fill Down value in Excel column (Vertical Fill)?
Filling cells makes work easy when you have to copy the same value or the same type of values in multiple columns. For Example,
-
- In cell A1, type number 1
- Now to get the same number in all cells in range A1:A10
- Option 1: You can type 1 in all cells in this range manually or copy A1 and paste in all cells till A10
- Option 2: Select a cell, there is a small box that appears in the right bottom corner. Hover over it, and +sign will appear. Click the left click of the mouse, hold it, and drag till cell A10
-
-
- Option 3, select cell A1, now press the SHIFT key, hold it and press the down arrow till you reach cell A10. Then press CTRL + D
- To get numbers from 1-10
- Type 1 in cell A1, 2 in cell A2
- Select both cells
- Now, there is a small box that appears in the right bottom corner. Hover over it, and +sign will appear. Click the left key of the mouse, hold it, and drag till cell A10
-
-
- Follow the same steps to fill other values like Alphabts, weekdays, months
How to Fill value to Right in Excel column (Horizontal Fill)?
- In cell D4, type number 1
- Now to get the same number in all cells in range D4:M4
-
- Option 1: You can type 1 in all cells in this range manually or copy D1 and paste in all cells till M4
- Option 2: Select a cell, there is a small box that appears in the right bottom corner. Hover over it, and +sign will appear. Click the left click of the mouse, hold it, and drag till cell M4
- Option 3, select cell D4, now press the SHIFT key, hold it and press the Right arrow till you reach cell M4. Then press CTRL + R
-
How to add a new cell in Excel?
• You can add a new cell, range, column, and row in excel
• Let’s start with adding a new cell.
• In your data, if you want to add a new cell in any row, select the cell where you want to add a new cell and right-click from your mouse. It will open a menu
• In the menu, select Insert
• It will open a new menu, asking for the position of the existing cell i.e whether you want to move the existing cell down/right of the new cell
• Click OK and existing cells will move to 1 cell right
• To add a range of empty cell
• In the table where you want to add a new range, select the range
• Right-click, Menu will appear
• Select Insert
• It will open a new menu, asking for the position of the existing range i.e whether you want to move the existing range down/right of the new range
• Click OK and the existing range will move to the right
Adding a new column
• To add a new column, in the existing table the process is the same as above
• Select the entire range by clicking on the column name
• In the below image, hover over column G, it will show a down arrow sign, and press the left click of the mouse. It will select the entire column
• Then right-click, and select Insert in the menu, it moves the entire column G to the right side.
Adding a new row
• To add a new row, in the existing table the process is also the same
• Select the entire range by clicking on the row number
• In the below image, hover over row number 12, it will show a right arrow sign, and press the left click of the mouse. It will select the entire row
• Then right-click, and select Insert in the menu, it moves the entire row 12 downside.
How to Delete cell from Excel?
• Same as insert method, we have a Delete option to delete any cell, range, column, or row in a table
• The process is similar to insert, the only difference is instead of selecting Insert menu option, select Delete option and it will show new popup, whether you to move the cell to left/down after deletion. For Example,
• As we can see in the above image after deleting cell G12, the value in H12 moved to the right side i.e. cell G12
• Same you can delete a range, a column, a row
How to Move data in MS Excel?
• There are chances when you want to move data from 1 cell to another cell, 1 range to another range, or from 1 sheet to another sheet
• There are multiple ways to do this
• Select the entire range, once you see the + sign press and hold the left key of the mouse and move it to the cell where you want to show the data
• Select the entire range, right-click from the mouse, and select Cut. A dotted line will appear around the table. Then go to the cell where you want to show the data and again right-click from the mouse and select Paste.
How to use Undo & Redo in Excel?
Excel provides an option to reverse your action
- Undo: Use Undo, to reverse your performed action
- You will find Undo icon in the Ribbon
- there is a shortcut key: CTRL + Z
- Redo: To reverse Undo operation
- You will find the Redo icon in the Ribbon
- there is a shortcut key: CTRL + Y
Excel Tips and Tricks – BONUS!!!
Some basic shortcuts
• Open dialogue box: CTRL+ O
• Close file: CTRL+ W
• Save file: CTRL + S
• Selecting multiple cells: SHIFT + UP|Down|Left|Right arrow key till your data
• Selecting entire column: CTRL + space
• Selecting entire row: SHIFT + space
• Selecting the entire sheet: CTRL + A
• Copy: CTRL + C
• Paste: CTRL + V
- Add a new column
- As we know, adding a new column moves the existing column to the right
- First select the column > CTRL + Space
- Then press CTRL + SHIFT + “+” sign
- Add a new row
- As we know, adding a new row moves the existing row to the next row
- First select the row>SHIFT + Space
- Then press CTRL + SHIFT + “+” sign
- Delete column
- First, select the column > CTRL + Space
- Then press CTRL + “-” sign
- Delete row
- First select the row > SHIFT + Space
- Then press CTRL + “-” sign