If you regularly use Microsoft Excel spreadsheets, you should familiarise yourself with the tools you can use and the actions you can take. You can then save time and achieve more.
Here are seven tips and techniques that you should master.
Image source: https://unsplash.com/photos/9yCYGgPe5Kg
1. Adding Multiple Rows or Columns
Every Excel user knows how to add a row or column. But do you know how to add multiple rows or columns?
It can be very useful to know how to do that, and it can save you a lot of valuable time; especially if you need to add dozens or hundreds of rows or columns to your Excel spreadsheet.
To add multiple rows or columns, simply highlight the same number of rows or columns you want to add that already exist and then right-click. Select “Insert”. It really is that easy.
2. Creating Drop Down Lists
Drop down lists can be very helpful features on Excel spreadsheets. By using a drop down list, you enable users to easily choose items from a predefined list. Thus, you can tailor your needs accordingly in various ways.
Check out this insightful guide to learn how to create a drop down list in excel.
3. Removing Duplicate Data Points or Sets
When you use large data sets, you could end up with duplicate content. For instance, you could have a list of contacts but only want to view a certain category of contacts.
If you want to remove duplicates, highlight the row or the column in which you want to remove the duplicates. Next, go to the “Data” tab and select “Remove Duplicates”.
If you are using the old version of Excel, you will find that under the “Tools” subheading.
From there, a pop-up will appear. Then, simply confirm the data you want to work with and select “Remove Duplicates”.
4. Adding Checkboxes
Inserting checkboxes into the column of an Excel spreadsheet can be very handy at times.
For example, you may want to manage your sales contacts and track whether they were contacted in the last quarter. Therefore, you could set up a “Contacted Last Quarter?” column and then check off the cells once you have called each contact.
The process is simple. Highlight the cell you want to add checkboxes to and then click “Developer”. Next, click the checkbox under “Form Controls”. When the box appears, simply copy it, highlight the cells that you want it to appear in, and paste.
5. Using the Format Painter
If you use the same formatting commands, again and again, you can waste valuable time. Instead, use the format painter. With it, you can quickly copy the formatting from one area of the Excel spreadsheet to another.
Simply select the cell that you want to replicate and then choose the “Format Painter” option from the toolbar, which appears as the paintbrush icon.
6. Hyperlinking Words
When you add a URL to an Excel spreadsheet, it should automatically become clickable. But there could be times when you want to add hyperlinks to things like specific words and page titles.
To add hyperlinks, first, highlight the words you want to add the link to. Next, press “Shift K”. A pop-up window will appear. Simply add the hyperlink into the box and click “Enter”.
Another option for adding a hyperlink is to manually highlight the cell and click “Insert > Hyperlink”.
7. Using Conditional Formatting to Automatically Change the Colour of Cells
If you want to do something like flag certain numbers, such as your top 10% sales, in your Excel spreadsheet, you can easily change a cell’s colour based on the information contained in the cell. You can easily colour code commonalities too. This is known as conditional formatting.
Basically, by using conditional formatting to make cells automatically change colour based on the relevant data, you can more quickly see information on the spreadsheet that is of the most importance.
To achieve conditional formatting, begin by highlighting the group of cells that you want to use. Go to the “Home” menu and select “Conditional Formatting”. Then, select or create the rule you require from the pop-up window that appears.
Once you are done, simply select “OK” and your results will automatically appear.
