Written by Alejandro Perez, Data Analyst
Microsoft Excel is a spreadsheet program that is commonly used to organize data and perform various analysis through the use of formulas, functions, formatting data, charts and graphs, etc. There are several other spreadsheet programs, but Excel remains the most widely used software program by businesses. However, it is not only limited to large companies, but small entrepreneurs and college students are using it for their day to day work. The fact is that everyone must have some basic knowledge of Microsoft Excel and to help you I have compiled this guide of tips and tricks which, I believe, will be useful in completing your everyday task.
Before we begin, there are three important components of Excel that need to be understood. A cell is the most powerful part of a spreadsheet because you can enter data such as a text, a number, or a date into a cell. Every cell is identified by its cell address which is made up of its column number and row number. A worksheet is made up of individual cells and can also hold charts, images, and diagrams. A workbook is made up of one or more worksheets and you can add or delete worksheets, hide them within the workbook, and change the order of your worksheets within the workbook.
The first excel tip I want to share with you is called Conditional Formatting. This is a feature that allows you to apply specific formatting to cells that meet certain criteria. Most of the time is used as color-based formatting to highlight, emphasize, or differentiate among data and information stored in a spreadsheet. This type of formatting calls attention to important data points such as deadlines, budget items, duplicate values, and makes large data sets more digestible. Conditional formatting can be applied by performing the following steps:
Open an existing spreadsheet with data. In this example, we will use conditional formatting to identify all duplicate values in a column. (Please note that the sample of data used in the examples is fictional data utilized for training purposes only).
To apply highlight rules, select the range of values you want to apply a rule to. For this example, we want to highlight all individuals with the same First name. So select the values in the First Name column (F2:F10)
From the Home tab, click Conditional Formatting on the right side of the toolbar, and click Highlight Cells Rules from the dropdown menu. Click Duplicate Values.
A box will appear. You can select the formatting you would like for the duplicate values and click OK.
Your spreadsheet will now reflect this highlight rule, with the individuals with the same First Name.
The second excel tip is Flash Fill. Say you have two columns of names and you need to construct email addresses from them all. Just do it for the first row and Excel will work out what you mean and do it for the rest. Begin by typing out the email address you want to create using the name and once you go on to the next record, Excel will automatically identify a pattern and the flash fill option will appear.
The third excel tip are keyboard shortcuts. Shortcuts are used to expedite common operations by reducing input sequences to a few keystrokes. There are well over a hundred shortcuts in excel but below are the most useful ones in my opinion.
The fourth excel tip is utilizing Filters. Filtering effectively hides data that is not of interest. Usually there's a value (e.g. Date of Birth) that you're looking for and Filters will bring up those and hide the rest. In addition, you can also filter on number values (e.g. is greater than, top 10%, etc.), and cell color. Filtering becomes more powerful when you need to filter more than one column in combination and is fast and effective. The shortcut for adding filters is Alt+D F F.
The fifth excel tip is creating a drop down list using Data Validation. Data entry is quicker and more accurate when you use a drop down list to limit the entries that can made in a cell. To create a drop down list in Excel, you need two things: A list of values (contained within a cell range) and a blank cell to use as the data entry cell.
Let’s begin by creating a list of values that we want to use as our drop down list options.
Select the cell in which you want the drop down list and then go to the Data tab and click on Data Validation and choose List from the Allow option’s drop down list.
Click the Source control box and drag the cursor to highlight the cells A1:A3. Alternately, simply enter the reference (=$A$1:$A$3).
You will now see the dropdown menu in the cell in which was selected. You can add the drop down list to multiple cells below the selected cell by dragging the drop down list.
I hope this excel tips guide has been helpful and you find that these tips assist with your day to da activities.