In today’s business environment, people are being asked to do more and more. The ongoing labor shortage means that there’s more work than can be done by a single person, but they’re asked to do so anyway.
The only way to stay ahead is by becoming more efficient to accomplish more in less time so you can head home at a regular time and spend more time doing what you enjoy.
Microsoft Excel is one of the most powerful tools for data analysis and reporting, but its full potential is often not utilized due to not knowing that features exist and not know what is possible.
By learning some simple tips and tricks, you can use Excel faster, streamline your work processes, and get more done in less time. In this article, we will share some practical ways to use Microsoft Excel faster, whether you’re a beginner or an experienced user.
Learn the Most Essential Excel Keyboard Shortcuts
Using the keyboard as your primary way to navigate Excel will save you hundreds of hours a year and make you look like a total badass in the office. People unfamiliar with Excel will be in awe and people that don’t use Excel will be perplexed.
Move Between Cells ARROW KEYS
Move to end of Row or Column CTRL + ARROW KEYS
Move + Select end of Row or Column CTRL + SHIFT + ARROW KEYS
Select Active Excel Row SHIFT + SPACE
Select Active Excel Columns CTRL + SPACE
Insert Column in Excel CTRL + SHIFT + PLUS
Delete Column in Excel CTRL + SHIFT + MINUS
Switch Between Excel Workbooks CTRL + TAB
Switch Between Excel Tabs CTRL + PG UP (Or PG DOWN)
Edit a formula in a selected cell F2
De-select a formula that you selected ESC
Start a formula from the keyboard Type = to begin writing a formula
Switch a relative to absolute formula F4
Quick Tip: Once you are editing a cell you can use CTRL + ARROW KEYS and CTRL + SHIFT + ARROW KEYS to move between words, or to select parts of words and formulas.
Excel Keyboard Shortcuts: Combine for Ultimate Speed
If one Excel Keyboard shortcut is a time saver, combining them one after the other will save you ever more time. Here are some common scenarios in Excel and how to use keyboard shortcuts to complete the task.
How to Copy and Paste in Excel
Press “Ctrl + C” to Copy. Then press “Ctrl + V” to Paste
How to Insert rows in Excel
Press “Shift+ Space” to Select a Row
Then Press “Ctrl+ Shift + +” to Insert a Row
How to Insert Columns in Excel
Press “Ctrl + Space” to Select a Column
Then Press “Ctrl+ Shift + +” to Insert a Column
By using these shortcuts along with navigating using “Ctrl + Arrow” Keys you will fly around Excel workbooks at lightning speed.
Learn how to Resolve Common Excel Errors
As you become more and more proficient in using Excel, you are going to come across errors. Follow this training to understand how to fix them, and how to avoid them when they arise.
Data Types in Excel Make a Big Difference
You can only perform certain calculations on columns depending on the data type that the column is. You’ll notice this if you ever try to add a couple of numbers together and you don’t get the right results. A scenario like this typically occurs when the cell or column in Excel are being read as text. Excel does not know how to add text together, even if the cell clearly contains a number. You have to convert the cell to a number before being able to apply mathematical operations to it.
How do you convert text to numbers in Excel?
If you have a column of numbers that show up as text you can convert them a couple of different ways.
Option 1.) Select the cells, and click the box on the top left to convert to number.
Option 2.) Paste Values and Multiply by 1. This is the preferred method when working with a large column of data. If you use Option 1 many times Excel will crash or take a very long time to process.
- Type the number 1 into a cell
- Copy the Cell
- Select the text cells and right click > Paste Special
- Paste Values and Choose Multiply
How do you force a number to be text in Excel?
Placing an ‘ in front of a number will force it to be text. If a green arrow shows up in the corner of an Excel number, it means that the cell is text.
How do you fix a large Excel file?
Ever wonder why an Excel file is so large? Sometimes it’s an issue with the workbook having cells that are active but blank. The first thing to check if your Excel file size is larger than expected is for active cells with no information in them. Use the CTRL + END keyboard shortcut to identify the last active cell in a workbook.
If you find one, you can delete the blank columns or rows. Navigate around the workbook using CTRL + Shift + Arrow Keys to select the blank columns or rows.
Option B.) If Excel files are too large and there are no extra active cells, you can try saving the workbook as a .xlsb instead of a .xlsx it’s an Excel Binary file which typically has a smaller file size.
Option C.) Save the Excl file as a .csv sometimes it will produce a smaller file. It could also make it easier to work with later on in your process. Be careful though, a csv will only save a single sheet and you will lose all other data in the workbook.
How do I copy formatting from an Excel cell above?
Use this technique when doing quick reconciliations. Highlight a cell as green or yellow and you can use the keyboard to copy the cell format down to the active cell! Press down on the keyboard then F4 to copy the formatting of the cell above.
Copy down the most recent cell format F4
Easily Move Excel Columns and Blocks of Cells
A little-known trick is that you can hold down SHIFT and mouse over the side of a selected range of cells. The mouse will turn into a four-way arrow which lets you drag and move cell ranges. This allows you to move parts of your data around an Excel worksheet, or it allows you to move full columns or rows without using a cut and paste function.
Pay Attention to New Excel Features on the Horizon
Excel is no spring chicken. It’s been around the block and seen a thing or two. That doesn’t mean that Microsoft has given up on it. They continue to extend the usability and feature set. Microsoft publishes the Microsoft 365 roadmap which is their rebranded name of Office 365. You can find all the recent and upcoming updates here: Microsoft 365 Roadmap | Microsoft 365
There are also many features that go underutilized. We’ll outline a few of them for you in case you haven’t had a chance to explore them.
Excel Get and Transform will Save You Time
One of our favorite features is Get and Transform. These are expert level Excel features that most people never take the time to learn. The good news is that Get and Transform is not that hard to learn. If you invest some time in learning how to prepare data, you will find yourself spending less time opening Excel and be amazed at how much it can do.
Example Use Cases for Excel Get and Transform
- Data Integration: Combine data from multiple sources into a single Excel worksheet.
- Data Cleaning and Transformation: Standardize data and modify it as necessary to ensure consistency and accuracy.
- Data Filtering and Sorting: Filter and sort data based on specific criteria.
- Data Aggregation: Summarize and analyze large data sets using calculations such as summing and averaging.
- Pivot Table Creation: Create pivot tables for powerful data analysis and insights.
Learn Microsoft Power Query for Excel
One of the reasons that we love Power BI so much is that you can easily export published datasets to Excel. Conversely you can do a lot of data preparation that you would do in Power BI using Power Query for Excel.
Power Query or the “Get Data” button in Excel allow you to connect directly to outside data sources. This feature is a huge time saver. Think about the time you spend collecting spreadsheets or downloading them to consolidate when you could connect directly to the data.
Here are some of the data sources you could connect to directly from Excel:
Microsoft Access
- Microsoft SQL Server
- Web Pages
- Power BI Data Sources
- Cloud Services such as Azure
Exciting New Excel Formulas
Excel is aways being improved, a few of the most recent formula additions to the platform are formulas for working with text. These formulas are more dynamic that using a left, right or mid function and can be setup to pull text based on another text pattern in the entire sentence or string.
- TEXTBEFORE
- TEXTAFTER
- TEXTSPLIT
Microsoft updated the Lookup Functions in Excel to eliminate a lot of the common pain points. For Example, use XLOOKUP to find matching records without the data itself being to the right of the starting column of a VLOOKUP.
- XLOOKUP
- XMATCH
An Open Love Letter to Microsoft Excel
There are many consultants, and software vendors that state one of their primary goals is to eliminate Microsoft Excel. We couldn’t disagree with this point of view more. As accountants, we’ve had a crush on Excel since the first time we met in college. Then after college when the day didn’t go our way, you know who was there to keep us company and help us feel better late at night? Not our significant others, friends or family, but that little green X logo.