Top 10 Excel Tips (with Instructions on How to Implement Them)
Megan Seal and Juania Owens
With nearly a decade of Excel experience, it’s no wonder Megan Seal is the resident Excel expert and guru on the Advanced Management Strategies Group’s (AMSG) Department of Veterans Affairs’ (VA) Office of Information Technology’s (OIT) Program Planning and Oversight Support (PPO) Contract.
Introduced to Excel in college, Seal was tapped to join a teaching practicum following her Microsoft Applications course where she passed with an overall score of 106 (out of 100!). During her teaching practicum, she held open lab sessions to help students complete homework assignments and study for exams. During her first job out of college, she quickly realized the content she learned and excelled in during college did not translate into real workplace scenarios. She was a beginner again.
She found herself searching Google to figure out new tips, tricks, and formulas to create various reports and to streamline her Excel reports as well. Piece-by-piece she expanded on and built her Excel knowledge and continues to learn each day. From comparisons to data analysis, she uses Excel daily to organize and present OIT’s PPO data as efficiently as possible.
Here are Seal’s top 10 Excel tips (with instructions on how to apply them) to help create and improve your Excel reports.
1. Use Macros to Automate Repetitive Tasks and Save Time
Macros is an action or sets of actions that can be named, saved, executed, and recorded or coded as many times as required and whenever desired. Although they are infamous for being hard or complicated, recording a macro is as easy as clicking a button, which can save users hours of time.
Here’s a scenario: Each week a user receives raw data that must be inserted into a table with specific column orders, column widths, row heights, sort requirements, and text alignments. Instead of formatting the data each week, a macro can be recorded once to automate this task for future reports.
To create a macro: Navigate to the Developer Tab and select Record Macro (Image 1). Name the macro and, if you wish, provide a shortcut key or brief description. Exit the prompt screen and perform the actions you wish to automate. Next click Stop Recording (Image 2). The macro is now saved for future use. It can be run by clicking the Macros button and selecting the applicable macro name.
(Image 1 – Record a Macro)
(Image 2 – Stop Recording a Macro)
2. Set Up One Workbook to Compare and Identify Data Changes
Whether performing quality control data checks or reviewing a spreadsheet’s latest set of updates, there is an easy trick to quickly compare changes between two different data sheets.
Here’s a scenario: You send out an Excel spreadsheet to your department and it is returned without having the changes highlighted. You need to quickly compare the original spreadsheet to the one received to indicate the updates.
To compare and identify data changes between two sheets: First things first, it’s important to note, this trick will only work if no new rows or columns have been added to the original sheet. On separate tabs, include the original sheet and the new sheet in one Excel workbook.
On the new sheet’s tab containing the data changes, highlight the entire spreadsheet. Under the Styles Section in the Home Ribbon, select Conditioning Formatting and New Rule… from the dropdown menu (Image 3).
(Image 3 – Conditional Formatting in Excel)
The formula, =A1<>Original!A1 (Image 4), is used to compare data between two sheets within an Excel Workbook.
(Image 4 – Excel Compare Formula)
In the New Formatting Rule pop-up box, insert the formula in the Format values where this formula is true: field (Image 5). Click the Format… button. Select a color under the Fill tab and click OK. Click the next OK button to apply the changes. With this new formatting rule set, the new sheet’s tab should now identify and highlight all changes between the original sheet and the new sheet in the chosen fill color.
(Image 5 – Creating the Conditional Format)
3. Implement VLOOKUPs to Save on Manual Data Entry
The VLOOKUP is probably one of Excel’s most popular and powerful functions around. VLOOKUP stands for: Vertical Lookup. The Vertical Lookup function is to search for a specified cell value by searching for it vertically across a spreadsheet. And although it has a reputation for being complicated, once mastered, it is a very useful and easy tool to use.
Here’s a scenario: A report (spreadsheet) is missing a specific column of data that is in another report. You need to include that missing data from the other report into your current report without having to manually enter the data. Here’s where the VLOOKUP function comes in to play.
To implement the VLOOKUP function: It’s important to note that the two spreadsheets must have a unique identifier that tells the rows apart and can link the reports together. In most cases, this is an ID. It’s also important to note that the identifier used in the formula is in the first column referenced for the secondary data source in the formula.
The VLOOKUP function requires four pieces of information:
A. The cell location of the unique identifier
B. The table range of the secondary data source (starting at the column that holds the unique identifier and ending with the column that holds the information being brought over)
C. The number of the column that holds the data you wish to return
(NOTE: this is the column number of the data range you provided for the second portion, NOT the actual spreadsheet column number)
D. TRUE or FALSE – TRUE returns an approximate match, and FALSE returns an exact match
Image 6 displays an example of a VLOOKUP formula used to pull in the fruit color from one report to another. The formula used: =VLOOKUP(A3,$E$3:$F$6,2,FALSE)
(Image 6 – VLOOKUP Function Example)
4. Use Keyboard Shortcuts to Improve Work Efficiency
Keyboard shortcuts are a great way to increase productivity while working in Excel. With just a click of a few buttons, these shortcut keys can perform major functions that dramatically increase speed and improve work efficiency. Here are a few popular (and useful) shortcuts:
Ctrl + N Creates New Excel Workbook
Ctrl + O Opens Existing Workbook
Ctrl + S Saves Workbook
Ctrl + PageDown Moves to the Next Sheet
Ctrl + PageUp Moves to the Previous Sheet
Ctrl + A: Highlights all Data
Ctrl + L: nserts Table for all Highlighted Data
Ctrl + ; Inserts Current Date
Ctrl + Space Selects Entire Column
Shift + Space Selects Entire Row
A list of all shortcuts can be viewed on Microsoft’s website at Keyboard shortcuts in Excel (microsoft.com).
5. Use the CONCATENATE Function to Consolidate Cells’ Data
When the data in a spreadsheet isn’t structured or combined according to your needs, Excel’s CONCATENATE function can solve this problem by joining two or more strings of data into one cell.
Here’s a scenario: A list of first and last names in two separate columns and cells need to be combined into one cell.
To use the CONCATENATE function: This function is relatively easy to use and only requires the cell locations of the cells you wish to combine with any extra formatting, like spaces or dashes, you wish to include.
Locate the first and/or second pieces of data to include. The third piece includes formatting – to include a space or dash in the consolidated data, include quotation marks (ex: ” ” , “-“) (Image 7) in the formula. Otherwise, the second cell location would just be included. Repeat these steps until finished.
(Image 7 – Example Formula: =CONCATENATE(A2,” “,B2))
6. Use the Text to Columns Function to Separate Data
Like consolidating (or concatenating) cells, sometimes the data in an Excel spreadsheet needs to be broken out into multiple cells. Using the Text to Columns function is a quick and easy way to accomplish this task. This is a great function to use when splitting cells if the cells are separated by a delimiter. A delimiter is any character that is used to specify boundaries, such as tabs, semicolons, commas, spaces, etc. This function has the ability to identify the delimiter in a cell and separate the values within that boundary into separate cells.
Here’s a scenario: You receive a spreadsheet that contains your employees’ full names, but you need to be able to sort it by their last names from A – Z.
To use the Text to Columns function: To begin using this function, it is important that you have entered enough blank columns for the data that is being broken out (Image 8). Otherwise, it will overwrite existing data. Once enough blank columns have been entered, highlight the column that contains the data, navigate to the Data Tab, and in the Data Tools Section, select Text to Columns (Image 9).
(Image 8 – Excel Text to Columns Function)
(Image 9 – Locating Excel’s Text to Columns Function in the Data Tab)
In the Convert Text to Columns Wizard – Step 1 of 3 pop-up box, select the Delimited radio button (Image 10). On the Step 2 of 3 screen, select or enter the Delimiter type. Once selected, a preview is shown in the Data Preview window (Image 11). Select Finish and the data should now be split into two separate columns (Image 12).
(Image 10 – Convert Text to Columns Wizard – Step 1 of 3)
(Image 11 – Convert Text to Columns Wizard – Step 2 of 3)
(Image 12 – Excel data separated using the Text to Columns Function)
7. Count Business Days Using the NETWORKDAYS Formula
While working in Excel, you may need to calculate the total amount of business days between two dates. This is where the NETWORKDAYS formula comes in handy. Two pieces of information are needed to use the NETWORKDAYS formula: 1) the cell location of the beginning date and 2) the cell location of the end day. Image 13 provides an example: =NETWORKDAYS(A2,B2)
(Image 13 – NETWORKDAYS Excel Formula)
8. Converting Numbers Stored as Text Back to Numbers
Users can run into unexpected snags when bringing in exported data into Excel and working with the external data. For instance, numbers may be incorrectly formatted as text. Numbers stored as text can be identified by a green flag in the upper left corner of the cell (Image 14).
To correctly convert the values, select the yellow exclamation prompt in each cell. However, this may not be feasible when working with large data sets. Therefore, to convert an entire column of numbers that are stored as text back to numbers, highlight the entire column, navigate to the Data Tab, select Text to Columns, and then select Apply. As mentioned above, Text to Columns can be used to split out columns, but it can also be used to convert an entire column of text to numbers.
(Image 14 – Example of numbers stored as texts in Excel. Image courtesy of Microsoft)
9. Freeze Multiple Panes to Easily View and Scroll Through Data
If you have ever worked in a large spreadsheet, you know how beneficial it is to have a table’s row and/or header column frozen to easily scroll through and view large sets of data.
In some cases, you may need to freeze more than the first row or header column. In this case, select either the column or row immediately beside or beneath the last column or row to freeze. Navigate to the View Tab. In the Window Section, select the Freeze Panes dropdown, and select Freeze Panes (Image 15).
(Image 15 – Excel’s Freeze Panes Function)
10. Multiple Sort
Sorting a table in Excel is as easy as clicking a button. But what happens when you need to sort the data in multiple ways? Thankfully, Excel has a custom Sort & Filter feature built into its Home Tab.
Here’s a scenario: A contract manager wants to sort a spreadsheet by Department and then by their employee’s PIV Expiration Date.
To perform multiple sorts: In the Home Tab’s Editing Section, navigate to the Sort & Filter button and select Custom Sort… from the dropdown menu (Image 16). From the Sort pop-up box, populate the Sort by parameters with the first column you wish to sort by. For every additional column you would like to sort by, click Add Level (Image 17). When finished, click OK, and the spreadsheet will update.
(Image 16 – Excel’s Sort & Filter Function)
(Image 17 – Excel’s Sort Function)