5 Excel Tips that Will Help Be More Productive You at Work
If your work involves dealing with data or numbers, I can predict with a certainty that you work with Excel spreadsheets regularly.
It is one of the best spreadsheet tools out there and has been since it came out in 1987.
Excel is used in almost all the big and medium companies. Even small companies and freelancers rely on it for day to day tracking and management.
If you work with Excel (of which there is a high chance), it makes sense to know how to use it to get work done faster and more efficiently.
In this article, you will learn five Excel tips that will help you be more productive at work.
#1 Use Format Painter to Copy Formatting
A lot of times, when you get the data from client/colleague or download it from the web, it is a bad shape and format.
It can be time-consuming to making the data look good and format it based on your project requirement or your company’s brand.
If you can relate to this, you should check out format painter that can easily copy paste formatting from one dataset to the other.
Here is how to use it:
- Select the cells from which you want to copy the formatting.
- Go to the Home tab and within the Clipboard group, click on the format painter icon.
- Now use your mouse to select the cells on which you want to copy that formatting.
This will instantly copy the formatting from the cells you selected in the beginning to the ones that you selected later.
Note that this only copies the formatting, and not the value.
#2 Lock the Headers using Freeze Panes in Excel
If you have worked with large datasets in Excel, you would know the trouble of going back and forth in the data to check what a data point represents.
This happens in the column disappear when you scroll to the right, and the headers in the row disappear when you scroll down.
This makes to quite difficult to handle this data set as it becomes difficult to keep track of the data points.
There is a cool feature in Excel that solves this problem – Excel Freeze Panes.
Here is how to use it:
- Select the cell above which you want to freeze the rows and to the left of which you want to freeze the columns. For example, if you want to freeze top row and leftmost column, select cell B2.
- Go to the View tab and in the Freeze Panes drop-down, select the Freeze Pane option.
Now no matter where you go in the worksheet, your headers would always be visible.
#3 Remove Duplicates from Data
Duplicate records (or data points) in your data can prove to be problematic if you’re using this data in calculations.
For example, if you have the sales transaction data and there are duplicate records, and you want to know the total sale transaction of a specific item, it will be calculated incorrectly.
Here is a quick way to get rid of all the duplicate records at once:
- Select the data set that has duplicates.
- Go to the Data tab and click on the Remove Duplicates option.
- In the Remove Duplicate dialog box, check the ‘My data has headers’ option (only if your data has headers), select the columns from which you want to remove the duplicates and then click OK.
It will instantly remove all the duplicate records and show a dialog box to tell you the number of records that have been deleted.
#4 Make Data Entry Quick With Fill Handle
Often, data entry have some parts that follow a pattern.
For example, if you are entering serial numbers, it would go in a series (1,2,3..).
Similarly, if you’re entering months, it will again be a pre-defined series (Jan, Feb, Mar…).
If you’re entering these manually, you’re wasting a lot of time. There is a tool in Excel that makes this kind of data entry super fast. It’s called the Fill Handle.
Let’s take an example of months and see how to use Fill Handle:
- In the two consecutive cells, enter Jan and Feb.
- Now select these two cells and bring your cursor to the bottom right part of the selection. You will notice that the cursor turns into a plus icon.
- Left click from the mouse and drag it down. It will fill all the cells till where you drag.
Similarly, you can do this with numbers and dates.
#5 Insert Current Date and Time
If you need to insert the current date or time in Excel, you can easily do that using keyboard shortcuts.
To enter the current date, use the keyboard shortcut – Control + ; (hold the control key and press semi-colon).
To enter the current time, use the keyboard shortcut – Control + Shift + ; (hold the Control and Shift keys and then press semi-colon).
Note that these date and time values are static. It means that these will not change automatically the next time you open the workbook.
These are my top five time-saving Excel tips that I use almost daily.
What are you favorite Excel tips? Share it with me in the comments section.