MS Office
I remember my computer education teacher telling me that no matter what job you do, Excel will come in handy. I took his word lightly at that time, but now I beg to differ.
MS Excel is one of the most productive tools out there. What if I told you that you could make the best out of it by learning a few tricks. Sounds interesting, isn’t it?
So, here I present some beautifully crafted Excel tricks that will increase your productivity while using the software. Grab your chance to impress our coworkers and let them think of you as a sorcerer.
What is MS Excel, and Why is it Used?
Microsoft Excel is a spreadsheet program that comes in the Microsoft Office Suite. Some of the other programs presented in Microsoft Office are PowerPoint, Word, Outlook, and more. Everyone who has used a computer has used or heard of Microsoft Office in their life. It is a power-packed utility tool presented in Windows.
A spreadsheet program is used to create tables, grids of texts, numbers and formulas for calculations, and much more. It is valuable for many businesses to track expenses, create a budget, fiscal planning, and similar tasks. You can use it to prepare charts and graphs as well.
Time Saving Tips for Excel
1. Add a Drop-down List
Adding a drop-down list in your sheet will save a lot of time entering the text manually and preventing anyone from putting an invalid value. Here is how you can add the drop-down list in Excel quickly.
- Firstly, select the cell where you want to add the list.
- Then, go to the “Data” tab and click on “Validation.”
- Now, select the “List” option under the “Allow” field.
- Finally, you have to mention the source cells to fetch the data under the “Source” option.
2. Compose Text with “&”
A database usually disperses the information to show the accuracy of data. For instance, it usually does not save the full name in a single cell. Instead, we use first name and last name as two different entities. Now, Excel has a fantastic formula that can merge the data into two columns.
Here is the formula for that: =A2&B2
It will combine the data in cells A2 and B2 in that order. If you want to add a space between the information, modify the formula as follows: =A2&” “&B2.
In case you want to combine three cells, modify the formula as =A2&” “&B2&” “&C2
Tip: To combine the data for the whole column, drag the corner of the cell downside, and Excel will adjust the value for the respective row number.
3. Use Table Objects
Excel is mainly used for calculation purposes, and converting your data into a table will ease your work to interact with it. Once Excel knows you are working on a table, it opens up better formatting options and automatic filters. A few more benefits of creating a table in Excel include quick adding of a totaling row, cycling through columns to add data, always visible headers, and more. Even the charts and graphs update themselves when you change the source data.
You can do all these tasks without creating a column as well. However, the table makes most things easy to perform, thus saving a lot of your time.
To convert the data into a table, click anywhere inside the data. Then, click on the “Insert” tab and select “Table” from the “Table” tab. Now, a dialogue box will appear asking whether the data contain a header row or not. Select your preference and click “OK” to continue.
4. Add Multiple Rows and Columns at Once
We often require adding rows or columns in Excel. It is a straightforward task to do so. You have to right-click on a row and select the “Add row” option to introduce a new one at that spot. What if you want to add multiple rows.
To add multiple rows, select the number of rows you want to add. For instance, if you’re going to add three rows after number 10, select numbers 11, 12, and 13, i.e., three rows after 10, and right-click. Now, select the add row option, and Excel will add three rows for you.
It works similarly for columns as well.
5. Copy Rows or Columns
You can quickly copy or move rows and columns in Excel by selecting and dragging. Start by selecting the column you want to move. Then go to either top or bottom end of the sheet. Now, hover at the border unless you see the four-arrow cursor. Move the column by clicking and dragging the cursor.
The procedure to copy a column or row is similar. Press “Ctrl” while dragging, and it will copy your column/row.
6. Generate Unique Set of Values
We encounter common values in the dataset regularly. However, we often require a single value only once per dataset. You can use the advanced Excel filter in such conditions. Follow the steps to know the procedure.
- Firstly, make sure your column has a header cell. Add a header text if necessary.
- Then, click any cell in the column.
- Click the “Data” tab.
- Select “Advanced” in the “Sort & Filter” group.
- In the dialog box, click the “Copy To Another Location” option.
- Recheck if the listed range is correct.
- Now, select the “Copy To” box and enter the first cell number where you want to copy the information.
- Check the Unique Records Only option.
- Finally, click “OK” to create a list of unique values.
7. Return to the Active Cell
Imagine you are browsing through a large sheet, and now you are lost in the data. It doesn’t sound like a fairytale, does it?
There is a quick shortcut that will bring you back to the active cell in no time. Press “Ctrl+Backspace,” and you will get back to where you started. However, there is a catch. This shortcut does not work if you have clicked on another cell accidentally while browsing.
This shortcut comes in handy if you are working on a large sheet that goes beyond the screen.
Tip: I prefer coloring a cell if I have to come back to it again and again. It is easy to locate the cell by highlighting it.
8. Import Table from Web
This trick is one of my favorites, and it has saved me a lot of time. Consider you have to copy tabular data from a webpage. Usually, we would copy and paste it and then dive into the formatting options to look aesthetic.
However, you can quickly import a table directly from a webpage without much effort without leaving the program. That will save a lot of distractions as well. Here’s how to do the same.
- Start by clicking on the “Data” menu option from the ribbon
- Now, click on “From Web.”
- In the browser window, enter the URL from which you want to import the data.
- Hit the “Go” button on the browser window
- Scroll down to the table of your choice
- Tap on the “Click to select this table” check box
- Select the “Import” option
- Finally, click on “OK.”
That’s all. You have successfully imported a table from a webpage to your Excel Sheet.
Bottom Line
We discussed some of the quirky techniques in MS Excel that can save a lot of time. There are many more secrets to Excel than we can imagine. However, talking about all of them in a single article is not possible. Therefore, I picked a few from the list that I think you can use daily.
