A Pro’s Guide to Excel: 5 Easy Tips

Source: Wikimedia Commons

Spreadsheets are critical for storing and sorting your personal or business data. Although there are many players in the field, Microsoft (MS) Excel spreadsheets still reign dominant. With over 750 million users, it’s one of the most successful and crucial Microsoft consumer products.

Of course, throw that argument to any techie’s face, and they’ll dismiss it outrightly. Their dismissal is understandable given most data sorting and storage are bundled with business intelligence (BI) systems.

But MS Excel is still a powerful spreadsheet with over 400 functions across 12 categories, including database, math, and text. It allows you to analyze and visualize your data to uncover insights.

In short, it can handle broad data analysis and applications from marketing to accounting. For example, you can use it to create marketing budgets, sales leads calculators and income statements. But its many functions can be quite intimidating for beginners to master.

This blog is a pro’s guide to Excel discussing 5 easy tips. You will learn how to filter data, create simple to-do lists, remove duplicates, and create drop-down menus.

Let’s dive in.

1. Add an Extra Row or Column

You may realize that you need an extra row and column. Adding rows or columns one by one can be exhausting. You can easily add rows and columns without breaking a sweat.

If you want 5 or 10 rows and columns, highlight the same number on existing cells. Next, right-click and choose “Select” — it will automatically add extra blank rows or columns.

2. Create Simple To-Do Lists and Planners Using Form Controls

Microsoft Excel has checkboxes that allow you to make simple to-do lists and periodic planners. It also allows you to create interactive charts and reports. With a checkbox, you can visually display which tasks are completed or incomplete at a glance.

It is important to note that you can use two types of Excel checkboxes – the form and ActiveX control checkboxes. The latter relies on visual basics for applications (VBA) for functionality.

The former, form control, relies on Excel formulas and linked cells for functionality. The form control checkbox in Excel is easier to use. You can leverage it to create simple to-do tasks.  

Let’s assume you wish to create and track six tasks. The tasks could be books to read, projects or courses to undertake, etc. The following steps can help you enlist and track the tasks using the form control checkbox in Excel:

1. Include a Developer Tab into the Ribbon. Checkboxes are added from the Developer Tab, which may not be visible to every open Excel window. To add the Developer Tab, right-click an empty space on the ribbon (tabs on top of the Excel window).  Choose Customize the Ribbon as shown.  

In the Customize the Ribbon section, go to the Main Tab section and check the Developer option.

After clicking OK, you’ll see a Developer tab on the ribbon as shown:

2. Insert a checkbox to create a to-do list. Select the above Developer tab and click on the Insert icon. You’ll get a drop-down menu; select Form Controls on the menu.

Next, place the cursor in the cell you want the checkbox to be. The checkbox will appear after clicking on the cell.

                                   

3. Remove the text from the checkbox. To delete the checkbox’s text, press Ctrl and highlight the checkbox. Next, right-click to open the Context Menu, select Edit Text, and delete the text (click on the backspace key).

                               

4. Copy the checkbox to other cells. You can copy the cell containing the checkbox by clicking the cell, then pressing Ctrl + C. Next, select the cells to which you wish to paste the checkbox, then Ctrl + V.

                             

5. Link the checkbox to cells. We will add a new column (Completed?) between the Tasks and Status columns using Tip 1. Next, press Ctrl, select the first checkbox, go to the formula bar, and insert the “=” sign.

Then select the cell you want to link, in our case, cell C5.  Next, hit Enter to link the checkbox to Cell C5.

Repeat the procedure to other checkboxes to link them with the respective cells.

So, you can easily create simple to-do lists using form control checkboxes.

3. Simplify Data Using Filter

You don’t necessarily need to check a broad data set if you’re only concerned with specific criteria or areas. That’s where filters come in handy.

Filters allow you to focus on specific rows and data at a time. To filter data in your Excel spreadsheet, go to the Data tab, select the column you want to check, and then hit Filter.  You can organize your data in ascending or descending order if you click the arrow next to the Filter’s header.

You can also get specific details on items on the selected column as shown:

Selecting the items column and only checking “Binders” gets me this detailed data on Binders.

This is the detailed info on Binders after filtering the sales data.

4. Create Drop-Down Menus

It can be chaotic if you track some qualitative aspects of your operations or items. For example, you may wish to include words like “Yes,” “No,” “Sales Lead,” “Hot Lead,” “Cold Lead,” or “Prospect.”

You can easily mark such descriptive aspects of whatever you’re tracking using a drop-down menu in Excel.

This is how you go about it. Highlight the cells to which you want to include drop-downs. Next, click the Data tab in the top navigation, then select Validation.

Next, the Data Validation Settings box will open. Focus on the Allow options. Click on the Lists and choose the Drop-down list. Next, select the In-Check drop-down button and hit OK.

However, after sorting and checking your data, you may realize there are some duplicate data. How do you deal with that?  

5. Remove Duplicate Data

Duplicate data can mess up your entire data analysis or whatever you intend to use the data for. It is prevalent, especially when dealing with large data sets. Fortunately, Excel has simple tricks for finding and removing duplicate data.

To remove duplicates, go to the Data tab and click on Remove Duplicates on the right side of the navigation under Data Tools.

A pop-up will appear requesting confirmation on the data you want to use. Choose “Remove Duplicates,” and you’re off the radar.

Final Takeaway

Excel makes it fun to play with your data. But it can be intimidating to any beginner, given its many functions. What we’ve highlighted is just baby steps into being a pro.

Besides removing duplicates and adding extra rows and columns, you can undertake advanced uses on your data set to uncover insights and simplify your data. For example, you can automate your Excel data processes and uncover data insights using Pivot tables.

But you’ll need to set time for quick training to master some of these advanced-level Excel skills. It can be worth it, given that data is the “new oil.” Be well equipped to use it to drive personal and business growth.