When you’re first starting out in Excel, becoming efficient is an afterthought. Whether you like it or not, Excel seems to be the tool of choice for data manipulation, charting, reporting, and dashboards in nearly all workplaces simply for the fact that if you know how to use a mouse and a keyboard, you can learn how to use Excel. Excel becomes more and more powerful as you continue to use it and understand the available features. People love dumping there system data and information into Excel because of its flexibility.
If you are here now, that means that you use Excel in one capacity or another whether it be for work, your business, your home or for pleasure. There are staggering statistics out there that already prove that people who may be in the same position but who use their Excel skills for their position, can be paid up to 40% more (stats from 2012 – see Skilled Up article). Today I’m going to review some of the skills that you can learn right now that will help you become more efficient in Excel and make you stand out so that you can blow your fellow job competitors out of the water.
1. Get Efficient in Excel by Learning How to Use Shortcut Keys
I know. It seems like this would be an obvious gain in efficiency. That being said, I even catch myself under utilizing shortcut keys that, when you add up the time saved for each small operation, can easily equate to a couple of hours a week. When I finally started using some of the available shortcuts in Excel, I was blown away by how much time I was saving and also just simply on how much less of a nuisance certain operations were. For example, instead of taking your mouse and dragging and selecting an entire data set with let’s say 10 columns and 15,000 rows (you can imagine how long this could take), you can simply select a cell within the data set and type CTRL + A and the entire data set will be selected in a matter of a second.
So what can you do to practice your skills and start learning new shortcuts to get efficient in Excel?
Step 1: Review this Beginner Tutorial on 29 Excel Skills and Tips you Need to Learn
As a starting point, check out my blog post and video on 29 Excel Skills and Tips you Need to Learn – A Tutorial For Beginners. I compiled this video with beginners in mind but some of the skills may be new to intermediate users as well. It personally took me quite some time to gain some of these simple skills, simply because I wasn’t aware that they existed. Once you get through that list, you’ll be more efficient than the vast majority of Excel users.
Step 2: Get a Hard Copy of all of the Excel Shortcut Keys
I’m not going to lie. There are an overwhelming amount of Excel shortcut resources out there but I would highly suggest that you bookmark this one. David Bruns from Excel Jet has taken the time and effort to put together a comprehensive list and guide that is separated into sections such as navigation (my personal favorite- biggest time savers), extended selection, cell edit mode etc. and has also provided the ability to search and download a PDF for your keeping. In total there are 222 keyboard shortcuts for Mac and PC users.
222 Excel Keyboard Shortcuts for PC and Mac
[et_bloom_inline optin_id=optin_1]
2. Use Excel Tables and Name Ranges Wherever Possible to make Formula Writing and Other Features of Excel More Efficient
I am personally addicted to using name ranges and tables in Excel. Both are really awesome tools for simplifying how you create formulas and also how you interact with other features of Excel like pivot tables. Using structured data references (table name and field/column references) and name range references allows you to make your formula writing more efficient by allowing the following:
You can Reference Data Ranges Using Named Ranges and Structured References in Your Formulas
You can use the name of the name range in your formula reference and you can use the table name and column/field name combination when referring to tables in your formulas. You no longer need to reference fixed cell ranges (i.e. C7:C100) it becomes simply the name of the table and field/column or the name range.
Name Range Referencing
Structured Referencing (Tables)
Resources For Learning Structured Table Referencing and Name Range Referencing
Name Range References
Contextures Blog – Everything You Need to Know about Excel Name Ranges
Ozgrid Blog – Excel Name Ranges/Using Names in Excel
Structured References
Peletier Tech Blog – Structured Referencing to Identify Parts of Excel Tables
Excel Campus – Absolute Structured References in Excel Table Formulas
Use Structured and Name Range Referencing to Make your Formula and Pivot Table Data References Dynamic
This simple and easy to use tip will save you a ridiculous amount of time because you will no longer need to manually update your Excel formulas when there are changes made to your original data set. As you update your data set, because your formulas refer to the dynamic range, the formula results will update accordingly. No more changing the cell references manually! This same principle applies to pivot tables. Instead of having to update your data source, you simply need to refresh your pivot table when data is added.
How to Use Excel Tables for Dynamic Data Sources and Structured Referencing
As mentioned previously, there are two ways to make your data dynamic. You can use name ranges or you can use structured referencing with Excel tables. Below I’ve provided you an example of how to use structured referencing to make your formulas more efficient.
Not only can you use Excel tables to make your references dynamic with formulas, but you can also do the same with pivot tables.
Step 1: Once your table is created, you can now go to the table tools in the quick access ribbon (only when you have a cell within your table selected) and go to the tools section and select summarize with pivot table.
Step 2: The create pivot table screen will come up and you’ll see that the table/range has already placed the name of your Excel table in the field. Click OK.
Step 3: Now that the pivot table is created, as data is added to your Excel table, you no longer need to update your data source because it references the dynamic table.
How to Use Dynamic Name Ranges with Pivot Tables and Formulas
Using dynamic name ranges is also a useful tool for making your formulas, drop down lists and pivot tables more efficient. For me, I mostly use dynamic name ranges in conjunction with already built pivot tables so that I can create dynamic drop down lists. You can alternate and use the index formula as well as the offset formula to build dynamic name ranges. Below I’ll provide you with resources on creating dynamic name ranges to interact with pivot tables, formulas and drop down lists.
How to Create a Pivot Table With Expanding Data Ranges
You’ll learn a few different things from this article. You’ll learn how to create a dynamic name range using the offset formula and you’ll also learn how the offset formula actually works to do this.
How to Create a Dynamic Drop Down List Using the Index or Offset Formula
AbleBits does a great job at explaining how you can use either the offset formula or the index formula. The article reviews which method is more efficient and also explains why.
How to Use Named Ranges in Excel Formulas
This website shows you how to use name ranges in your Excel formulas. It also reviews how the name range box works and how you can use it in conjunction with your formulas.
Use Excel Tables and Named References to Make Your Charts Dynamic
Using Excel charts is an extremely useful way for graphically displaying your information and data to make it easier and quicker to understand. If you’ve ever built a chart in Excel using a static range reference, I’m sure you’ve realized that as you add additional information to your data set that your chart does not update. That’s where Excel tables and again dynamic name references can come in handy. Both of these have the ability to make your Excel charts dynamic.
Using Excel Tables to Create Dynamic Charts
Step 1: Create a table for your current data set (see above). Then highlight your entire table/data set by typing CTLR+A after highlighting one of the cells from your data set. Go to the insert tab and add a chart of your choice.
Step 2: Add another line of data to your Excel table.
Step 3: Watch as your table automatically expands to include your newly added data.
Create a Dynamic Chart in Excel Using Name References
Creating a dynamic chart using tables is slightly easier than using name references but there are some interesting things that you can do when using them. Jon Peltier is a guru when it comes to pretty much anything Excel but he’s developed some seriously cool ideas and ways to build dynamic charts. Have a peak at both of these articles and also be sure to browse around his website because you’ll learn a ton by simply clicking around.
Create a Dynamic Chart in Excel Using the Offset Formula
This article/blog post in particular reviews how you can create a dynamic charts using the offset formula with named references/ranges.
Chart the Last 12 Months Dynamically
This is easily one of my favorite blog posts as well as one of my favorite things to use especially when I’m applying and having to display a rolling 12 months (or any number of months) of data in a chart. This blog post will teach you how to do this dynamically all the while not needing to update your chart references by using named ranges and references.
3. Get Efficient in Excel by Using these 3 Built in Features
Being able to use some of the built in functionality that is already available in Excel can help you become a ninja when it comes to using Excel in your workplace. Excel has built in some extremely cool features that will allow you to get the answers that you need out of your data without much effort at all. Although I do have my favorites I wanted to give you 3 of the most basic features in Excel that will give you the most bang for your buck.
Feature 1: Excel Sorting – A Quick Way to Get Things in Order
When you’re looking at a massive amount of data, it’s never that much fun to manually sort through the hundreds or thousands of records that are in your file. This is where Excel sorting comes in to play. This is the feature that is going to allow you to sort numbers for a specific column from highest to lowest/lowest to highest or words from a to z or z to a. It’s pretty incredible to think that in a second you can resort thousands upon thousands of lines and records in Excel.
Learn the basics of Excel sorting by using this great article by Debra at Contextures
Feature 2: Excel Filtering – Display Only the Data You Need
Excel filtering has to be one of my favorite features because you can narrow down an entire data set based on the value of any given field (column) within seconds. Excel has made it very easy to apply a filter and although there are several different methods to add a filter to your data set, my favorite is to use the shortcut CTRL + SHIFT + L (works only with standard data set – filter is already included in pivot table fields). This option will add/remove a drop down to each of your header fields/columns within your data set. If you click on the down arrow, you’ll notice you can sort and/or use all sorts of filters including number filters and text filters. Not only can you filter a data set but you can filter pivot tables and Excel has also made available advanced filters which provides you with the opportunity to use cells above your data set to filter your data. I’ve included below several resources that will help you get acquainted with Excel filtering.
The Basic Excel Filter – From Excel Functions
Excel filtering is such an important feature of Excel and the beauty about this is that it’s really not that difficult to learn or use. Excelfunctions.net does a good job at showing you some of the more important features that are available to you when using Excel filtering. If we are talking basic filtering, one of my favorite options is the top 10 feature which allows you to show the top or bottom records of any given column. It does not have to be the top or bottom 10 either, this number can be changed to show however many records you want to display.
Tip: If you use the top 10 feature, you can use the sort option to make sure your top 10 or bottom 10 records are in the order that you’d like them displayed.
What Are Pivot Table Report Filters and How to Use Them – Chandoo
Chandoo does a great job at explaining why pivot table report filters are useful and then goes over some of the features that are available and how to use them. If you flip around Chandoo’s website you’ll also notice that he has some seriously cool and advanced tricks when it comes to using Excel filters. Checkout his all articles on data filters category page.
An Introduction to Advanced Excel Filtering – Contextures Blog
As you can see, I have been linking to this website in almost every section of this post. The reason why is Debra Dalgleish is a rock star when it comes to Excel. She has been able to compile a ridiculous amount of blog posts that go over almost, if not all key features available in Excel in detail. There is no exception made here. She has given you a thorough breakdown of how to use Excel’s advanced filtering option. My favorite option when it comes to the advanced filtering is using wildcards.
Feature 3: Excel Custom Number Formatting
Before we even get started in this section, I have one thing that I need to tell you about. If you want to display leading zeroes in a cell, simply type in an apostrophe before the number you place in a cell. This will make sure that your leading zero(s) is/are displayed (I know amazing, one of my favorites).
Now that we’ve got that out of the way, Excel custom number formatting allows you to change the way your numbers are displayed. You can do things like change a number that is input into a cell to be displayed in thousands or millions, you can add symbols and apply conditional number formatting to a range of cells, you can change the way your dates are displayed and you can literally change how characters are displayed in a cell despite what is entered. I’ve compiled a list of a few different posts that will be extremely useful for learning and building the skills required to apply customer number formatting daily in your work.
The Definitive Guide to Custom Number Formats in Excel – Excel Tactics
It’s always nice to have an all in one guide that you can refer to not only to understand a concept but then to also learn how to use it at a high level. Excel Tactics has done an excellent job at rounding up an amazing blog post that goes over all of the different custom formatting syntax requirements, and then also reviews all of the different techniques that you can use to change the way your cell values get displayed.
5 Incredible Number Formatting Tricks That Will Impress Your Boss
Not too long ago I decided there were a few different things that I really enjoyed using custom number formatting for so I personally decided to create a list of 5 amazing formatting tricks that I use and apply all of the time to my cells. In this article I review these extensively so that you can learn exactly how to apply these techniques in your day to day work.
A Technique for Practicing Your Custom Number Formatting Syntax – Chandoo
This is an awesome way of developing your syntax skills for custom number formatting in Excel. In this post Chandoo shows you how you can use cells and the text formula to automatically apply custom formatting to a value from another cell.
Alright, now that we’ve reviewed the 3 features that you need to learn let’s move on to our next method for becoming efficient in Excel, Excel Add-Ins.
4. Use Excel Add-Ins that Help Automate or Improve Your Work
Excel Add-Ins can be highly customized and tailored to a niche or they can be used in all industries. They can not only automate but they can also improve the quality of your work by providing you with rich features that don’t come out of the box with Excel. The best thing that you can do is Google the functionality that you are looking + Excel Add-In and you might find exactly what you are looking for.
If you’re interested in building your own Excel Add-In, be sure to checkout these easy step by step instructions on how to create an Excel Add-In from the Spreadsheet Guru (AKA. Chris Macro). If you’re still not finding what you’re looking for, you can have a peak at this list of 50+ mostly free Excel Add-Ins.
I have also included a couple of Excel Add-Ins below.
Excel Add-In 1: E2P – Create PowerPoint Slides in Seconds Instead of Copying and Pasting Charts and Tables
In a perfect world you wouldn’t have to build all of your monthly finance or operational slides for your monthly presentations but let’s face it, it’s reality. This Add-In allows you to easily populate and automate the transfer of Excel objects directly into an existing or new PowerPoint presentation right down to the pixel. As an incentive to get you started, Chris allows you to download a trial version of the software and test it out for 7 days.
Disclaimer: I am an affiliate of this product. Chris, the owner of thespreadsheetguru.com, does a phenomenal job at building products that not only work but seriously just kick ass.
Excel Add-In 2: CF Shapes – Create Stunning Excel Dashboards By Applying Conditional Formatting to Shapes
This next Excel Add-In was a joint effort between myself and Ryan Wells. I create and use shapes in my Excel dashboards all of the time. I’ve always wanted my shapes to change colors based on what value was being displayed in my shape (or the cell that the shape was referring to) so I decided to partner up with Ryan to create an add-in that would do just that. This add-in allows you to set a minimum and maximum value threshold so that when the value in the object exceeds, goes below or is in the middle of your min/max values, the shape/object will change color based on what the user has setup. You can see below what the add-in looks like when you’re getting things up and running:
5. Use Excel Templates or Dashboards to Become Efficient in Excel: The Build Once, Use Always Technique
When I first started using Excel I always found myself building the same reports over and over again using the same data set format and then changing and altering it to get answers to my business questions. It didn’t take me long to realize that I could build a template, dump my data into the template and quickly get the answers to my questions without having to manipulate the same spreadsheet over and over again. As I became better in Excel, I started to really enjoy building templates that saved time not only for myself but for my co-workers and bosses. For example, my boss asked me one day to automate a large set of charts that he had to create on a weekly basis. I set the charts up so that all of his charts would show a rolling 52 weeks and he simply needed to add new data every week without having to update the reference range for the 25-30 charts he required for his slides (see how here). This easily saved him a couple of hours a week because it meant he no longer needed to play around with all of the charts to get them to include the new data.
I’ve also used the same type of principal with building dashboards. You can build dashboards by simply creating a template Excel Table and your dashboard then automatically updates when the data changes in that table. Here two separate resources that will help you acquire some Excel dashboard and template creation knowledge.
5 Easy Steps on How to Make an Excel Dashboard
Learning how to build dashboards in Excel can be a little bit of a daunting task but it’s definitely doable. I’ve created a simple 5 step procedure that will walk you through the process of creating Excel dashboards. In this post, I’ve also added resources to other dashboard courses that you may want to consider taking such as Mynda Tracy’s Excel Dashboard Course (affiliated website).
Free Excel Templates, Dashboards and Lessons from Chandoo
Chandoo has compiled and built tons of amazing dashboards, templates and lessons over the years on his website. Many of these templates and dashboards he provides for free and does so with additional lessons and ideas on how to actually create them. This page is dedicated to providing you a long list of these free dashboards and templates.
Additional Excel Template and Dashboard Products and Links
⇒ Bradedgar.com – 5 dollar dashboards from bradedgar.com
⇒ Chandoo.org – Excel Dashboard Template – Premium Version (affiliate)
⇒ Chandoo.org – Excel Dashboard School Membership (affiliate)
⇒ The Spreadsheet Guru – The Waterfall Chart Creator
Conclusion
No matter how seasoned you are in Excel, there’s always room to learn something new. As you start to learn more and more in Excel, you’ll notice that you could easily go back to old spreadsheets and make them more efficient. To this day I find myself looking at old formulas or spreadsheets and saying to myself, I can make this better. That being said, I’ve reviewed and showed you 5 different ways to become more efficient in Excel.
Apply the techniques covered in this article and you’ll quickly realize how much time you can save during your day by applying some of these simple principles and concepts.
Be sure to leave comments with your favorite Excel efficiency hacks and also tell me how you used them to save time.
