27+ Ways to Teach Yourself How to Become the Excel Guru Everybody Relies On

27+ Ways to Teach Yourself How to Become the Excel Guru Everybody Relies On

 

Why is it so important to become an Excel guru?

Because everybody in the world uses Excel.

You can either be the person asking for help or you can be the person helping.

The best part about being an Excel guru is that you can bring your skills with you wherever you go.

Even if where you’re going isn’t hiring you for your advanced Excel skills, it’s a great icebreaker and you’ll quickly become everybody’s new best friend.

Here’s a mini library of ways you can teach yourself how to become the Excel guru that everybody relies on.

Be sure to you use the guru categories on the right to guide you through the sections.

[et_bloom_inline optin_id=”optin_7″]

Already a subscriber to the ultimate Excel newsletter?
Use the password that was sent to you via email to access the content.
If you are having trouble finding it, shoot me an email.

check

TODAY’S CONTENT UPGRADE: 

27 + Ways to Learn Excel Spreadsheet with Details and Links + Excel Resources Spreadsheet + Additional Links

27 + Excel Learning Techniques Spreadsheet

 

Become an Excel Guru by Practicing with Real Data Sets

 

Teach yourself using Excel data sets, really?

I know that’s what you’re thinking but in the real world and in business you use data sets to answer questions and solve problems. You need to be able to work with boring ol’ data sets and be able to turn them into something exciting.

In this section, I’ll provide you with a few hidden gems and resources that you can use to find test Excel data sets.

These are going to help you achieve that Excel guru status by giving you real information to play with and use to teach yourself those killer Excel skills.

 

1. Find and Use Open Data to Practice and Teach Yourself Excel 

 

Open Data Sets

Communities, cities, and countries are all making data publicly available for your consumption. If you live in a metropolitan area, there is a good chance you can go to google and type in your city name + open data and you’ll find it.

Exporting this data and analyzing it in Excel might upset you depending on the data set that you pull but at least you’ll be able to play around and teach yourself some awesome Excel tricks.

It’s important that you get your hands dirty with this type of real data.  Here is a short list of my favorite resources:

Data.gov – The Home of the U.S Government’s Open Data

Open.canada.ca – Canada’s Open Data Website

Open Data Toronto

Open Data New York City

2. Teach Yourself Excel Using Real Business Models 

 

Business Data Models

I stumbled across this website a few years ago and I’ve found it extremely useful for applying and creating real business models and data sets in Excel.

Database Answers

This website is great for understanding what types of data sets might be being used in a specific industry or sector that you’re working in or hoping to work in. Once you find the model you want to work with, you can create a data set in Excel by finding the field names that would be used for a specific table on that model.  Add the column headers and use some of the below resources to create your test data sets for practice:

Building Random Data Sets

Dummy Data – How to Use Random Functions

Once you’ve built your dummy data set for your model, you can start practicing your Excel skills by applying some formulas and techniques that you’ll learn in some of the other points in this article.

3. Teach Yourself Excel Using Data Sets You’ve Found Using the Google Filetype Operator

 

Filetype-xls search

It’s really as simple as the screen shot.

Head over to Google and type in filetype:XLS and then in quotations (this just means look for an exact match) find a certain keyword or type of data set that you’re looking for.

Again once you’ve got your data set, we can proceed to use this data for practical learning and testing in Excel.

4. Teach Yourself Excel Using Practical Business Sample Data

 

Excel Sample Data Sets

I’ve started to create Excel sample data to help provide my customers and readers with an alternative to having to create and find relevant business data to practice their skills in Excel.

These data sets are great for practicing and building Excel dashboards and should help you with building up your skills to Excel Guru status.

Here is an example of the type of dashboard that you could practice and build using these data sets:

Excel HR Salary Dashboard

5. Teach Yourself Excel By Importing Historical Stock Prices From Yahoo Into Excel

 

Historical Stock Prices Import Excel

Samir Khan at Invest Excel did a really good job of putting together a well coded import process directly through Excel to Yahoo Finance.  In this article he quickly reviews how you can use his excel template to quickly and efficiently import stock information without even having to leave your spreadsheet.

Samir was generous enough to give away the spreadsheet for free so that you can play around with his model.  You can download the Excel spreadsheet on his blog post page:

 Importing Historical Stock Prices from Yahoo into Excel

There are a couple of things you can do to ramp up your Excel learning:

 

1 – You checkout his VBA code to start learning how you can build and begin to learn how to import data from the web using VBA.  To do this, simply type ALT + F11 once you are in the spreadsheet. You can then review the modules as well as the code he has created on sheet1.

2 – Use the data set generated on the data tab to starting building your own investment models.  Think about ways you can use formulas like MAX, MIN, AVERAGE, INDEX etc.   I already have a ton of different ideas that you can implement to build an automated investment model that changes as you alter the historical data set.

This might be my favorite method for getting to know and understand some of the features that are available in Excel.  If you can begin to master and create models using this data, you’re well on your way to becoming an Excel guru.

You’ll need to think outside the box on what’s important when you’re investing.  Think about searching some of the major websites like Stock Trader to begin to understand the models and then maybe try and apply those concepts in your spreadsheets.

 

Bonus: Use the MarketXLS Excel Add-In to Learn How to Analyze Stock Data in Excel (Premium Product) [affiliate]

 

MarketXLS Software Excel

 

Being able to get a live up to the minute data feed as well as historical data for stock quotes into Excel at the drop of the hat is pretty remarkable. MarketXLS has done an amazing job at allowing you to pull in data, transform and analyze stock quotes and do things like the following:

 

  • Import Top 100 gainers / Top 100 Losers for the day (see left data set in screenshot above).
  • Import up to the minute stock quote data and dump it directly into Excel.
  • Dump historical data and perform analysis using every technical tool you could imagine.
  • Use and create visualization tools like candlestick charts and line charts.

 

MarketXLS Pro Excel Ribbon 

MarketXLS Add-In Tool for Excel

This tool of course allows you to do much more as well but the biggest and most exciting part about this is the ability for those who are looking to learn Excel to pull in real data into Excel and perform and learn how to analyze and perform research analysis directly in Excel.

If you aren’t performing or needing to do a lot of research analysis for the stock market, this tool might not be the tool for you but if your looking to get into investment banking (or already are), this might be a great product for you to teach yourself Excel stock analysis.

 

P.S. – I was able to work with MarketXLS to get 20% off all products that they provide – click any of the links in this post to get the discount.

 

Back to Guru Categories

 

Become an Excel Guru By Learning to use Excel’s Built-In Help Features

 

Yes.  Excel does have it’s own built in features to help you learn Excel just that much quicker.

Excel has thankfully built features into the application that help you in learning Excel formulas and other features at the click of a button.  Let’s dive into some of these learning techniques.

 

6. Learn About Excel Anomalies and Features Using the F1 Help Option 

 

F1 Excel Help

Surprisingly, built right into Excel is a help screen that provides you with a lot of useful information on how to handle common situations, issues, formulas and features in Excel.

These are often some of the most common questions users will have about using the application when they are first starting out but it also does contain some additional advanced help. To access the screen, once you are located anywhere within your workbook, simply hit the F1 key and the help program will launch automatically.

Fun Tip: You can use F1 when you’re in the middle of inputting code and depending on where you are, the method or property information will be brought up automatically for you so that you can understand how it works.

F1 Help VBA

7. Learn How to Use Excel Functions Using the Insert Functions Option (SHIFT + F3) 

 

Insert function Option

Learning Excel formulas should definitely be at the top of your priority list if you want to become an Excel guru.  A great way to do this is running through the list of available formulas and then using the help on this function link as shown above.

To access and use the insert function window, select a cell and then type SHIFT +  F3.  You can then search for a function, select a category and/or run through the list in the scroll box.  Once you’ve selected the function you’d like to learn, click on help on this function.

This page will give you the syntax and then also explain and give you an example that you can use for your learning process.

 

8. Learn Excel Formulas by using SHIFT + F3 during Formula Entry to Enter Function Arguments 

 

Function Arguments

If you know exactly what function / formula you want to use to solve a problem, you can type in the equals symbol with the formula name and then hit SHIFT + F3.

This will provide you with a description of the formula and also allow you to enter your function arguments individually with a description of what that argument is asking for.  This is great if you’re just starting to use a certain formula and you’re forgetting how it should be used.

 

9. Learn Excel Formulas by using Evaluate Formula to Understand why your Formulas aren’t Working 

 

Excel Evaluate Formula

When it comes to complex data models and spreadsheets, the evaluate formula can come in handy especially when it comes to nested Excel formulas.

Evaluate formula allows you to step into each step of the formula process to help you understand how the formula has either failed or to just give you an idea how the formula actually works.  This is great when starting to learn Excel for dissecting Excel business models and dashboards.

To access the evaluate formula window, select the cell that contains the formula, head over to the formula tab on the quick access ribbon and under formula auditing, select evaluate formula.

Once the window appears, you simply need to click evaluate and it will walk you through the formula steps.  You can then step into each section of a nested formula to get a true understanding of how the formula works.

10. Learn Excel formulas and Data Models by Showing all Formulas (SHIFT + `)

 

Show All Formulas

Being able to understand what formulas are being used in an Excel dashboard or model to build calculations is another step forward in becoming an Excel guru.

Using the show formulas option by either clicking show formulas under the formulas tab on the quick access ribbon or typing CTRL + ` will allow you to see how formulas are interacting with your data sets.  This will help you learn how to use formulas when building models.

 

11. Refer Back to Great Excel Resources using a Resource Tracker in Excel

 

Resources for becoming an Excel Guru

This may seem slightly trivial but you’ll realize very quickly that as you learn new Excel techniques, if you don’t reuse them, you’ll lose them.

By building a spreadsheet as you come across cool Excel concepts on the web, you’ll have access to links to all of your favorite articles.  The key here is to add a tag column as well as an Excel category so that you can easily filter and manipulate the spreadsheet to find certain articles or concepts as you need them.

Note that you could also create a shortcut spreadsheet for your reference.

Get the content upgrade with this post to access the Excel Resources Template.

Back to Guru Categories

Become an Excel Guru Using these Excel Resources

 

I know Excel resources seems like a pretty broad category but I’m going to focus my attention only on a few different ones in this section.

There are so many different platforms out there today that can help you learn Excel that either come with an investment or might even be free. In this section, I’ll review several different ways that you can use some of these amazing resources to speed up your path to Excel Guruness.

 

12. Learn Anything and Everything about Excel using Courses on Udemy 

 

Excel Courses on Udemy

Alright so here’s the deal, Udemy truly is an amazing platform and it does offer courses for a wide range of prices starting from free all the way up to serious amounts of money.

That being said with the review of their current pricing structure, Udemy is currently in the process of changing their pricing structure so that all courses are priced between $20 – $50 a piece with a maximum discount rate of %50.  The reason they are doing this is because it seems that around 90% of their sales are realized in that price range.

The planned implementation date of the launch will be in and around early April 2016.

With that in mind, you’ll want to signup to Udemy (if you haven’t already) and what you can do is start searching specific subjects in Excel that you are looking to learn.

From there, you can select whether or not you’d like to receive their newsletters with promotions and other information via email.  You’ll want to select yes so that you get the best deals on the courses you’re interested in.

Here’s a great way of adding and finding courses that suit your needs:

 

1 – In the search box type in “Excel” and hit enter.

2 – This will bring up by default a list of Excel courses that are available.  You can then filter the courses using the filters on the left hand side as shown in the screen shot above.

3 – Next you can also then sort based on reviews, language, high / low pricing, or newest.

4 – Once you’ve got that organized, you can select the course you feel fits your Excel needs by clicking on it.

5 – You can then add that course to your wishlist (or buy it of course).

 

The reason why I’ve said to add it to your wishlist is because Udemy is notorious for having sales on their courses and you might want to wait for one of their promotions to pickup the course you want to take at a discounted price.  This is why I mentioned making sure to receive their newsletters.

 

13. Become an Excel Guru using Free YouTube Videos

 

Excel YouTube Channels

Content is being created by the minute on YouTube and there definitely is no exception when it comes to Excel.

The beautiful thing about YouTube – the content is free (for the most part).

My Top 5 Active Excel YouTube Channels

#1: ExcelIsFun 

This is the staple Excel channel. I mean common, Mike Girvin has over 280,000 subscribers and 2,717 videos at the time of writing this article

#2: Contextures Inc.

Debra Dagleish has been an recognized as an Excel expert for a long time and her material speaks for itself.  She has some really great playlists like 30 Excel Functions, Excel Data Validation and Excel programming.

#3: Bill Jelen

Also known as Mr. Excel, has built up a massive database of Excel videos (1,717 videos) and really is just one of the best in the industry when it comes to teaching and training Excel. He has built some great Excel series/playlists on his YouTube channel like “Don’t Fear the Spreadsheet”.

#4: Excel TV

Rick Grantham, Oz Du Soleil and Jordan Goldmeier video chat and discuss Excel topics with some of the best experts in the industry including themselves.  Each of them bring something unique to the table.  Their videos can be extremely informative and they also help you in getting to know some of the Excel experts that are out there in the industry. This channel is all about giving back to the Excel community.

#5: ExcelTutorials – from Chandoo.Org

There is one person in the Excel game that is hard to leave out and that’s Chandoo (Purna Duggirala).  He’s done an amazing job at building up one of the most successful blogs on the internet and to be frank also just knows how to run a business all the while driving and bringing value to is followers and customers.

You can check my YouTube channel- bradedgardotcom out where I try to keep up to date on the regular.  My focus is typically on using practical Excel examples which helps with applying Excel techniques to real life or practical business situations.

This is definitely a condensed list of YouTube channels but with the help of Excel TV, you’ll find some of the greatest Excel experts out there.  There are many more that I would have liked to include but we need to continue on your quest to helping you become an Excel guru.

 

14. Organize Your Excel Kindle Book Notes and Highlights into Evernote

 

Excel Kindle Notes Highlights Export to Evernote

First of all, for those of you who aren’t using Evernote, you need to get on it now.

I purchase Excel books through Amazon’s kindle option all of the time for the simple fact that I can highlight and clip my notes and then easily revisit those notes.

The beauty about creating these highlights and then exporting them into Evernote is they become ridiculously easy to search for and find when you need them.  I’m not going to go into too much detail on Evernote because that’s not what this article is about.  But you do need to consider it if you’re reading Excel books often.

Checkout this blog post to get yourself started:

How to Get Your Kindle Highlights into Evernote

 

15. Find and Pay Money for the Excel Courses That Best Suit your Needs

 

MOTH - Power Query Course                 Chandoo - VBA Course

Paying money to find courses often might be a last resort for many because there is just so much information out there on the internet that you can get without having to pay any money.

The problem is you’re never getting the full story and you’re working hard on your own trying to piecemeal what you’re learning to get the overall picture.  If you strategize and pick the Excel course for you, you’ll be pleasantly surprised that you can learn and become an Excel guru quickly on that specific subject.

Here are some of my favorite courses from some of the best teachers and trainers in the Excel industry (note that some are affiliates and I will be sure to place in brackets for full disclosure).

#1 – Power Query for Excel – By Mynda Tracey [Affiliate]

This course is for those who spend a lot of time manipulating and cleaning data.  With this course you’ll learn how to automate and import your data into Power Query in Excel (you’ll have to have Excel 2010 or later) so that you will no longer need to spend your time cleaning up data just so that you can perform your analysis.

#2 – Excel School + Dashboards + VBA – Chandoo [Affiliate]

Learning, and understanding dashboards and VBA in the real world can act as a serious competitive advantage over your colleagues.  In this course, Chandoo and his team dissect and show you everything from how to apply and use loops and conditions, using variables, working with cells and ranges, databases , pivot tables and user forms and finally they also take a deep look into integration with other office applications. The super package is what will get you pretty much everything you need to learn.

Chandoo also has a whole host of other Excel learning products that are seriously beneficial.

#3 – The VBA Pro Course – Jon Acampora at Excel Campus [Affiliate]

Alright so Jon has over 25,000 active subscribers on his website and creates some seriously amazing training programs in Excel.  His VBA pro course is bar none one of the best VBA courses out there.  That being said, as of right now, Jon’s course is closed and opens up only on occasion so you can click the link above and scroll down to the bottom where you can be notified when course enrollment open ups again.

#4 – Excel Mini Course – How to Use Excel Slicers to Sort Top 5 / Bottom 5 – Brad Edgar – (Get 25% Off with Coupon Code: learnexcel)

This is just a small course I put together that shows you how you can use the rank formula with Excel slicers to build a top 5 / bottom 5 Excel dashboard. This is a really slick way to show your top 5 or bottom 5 records using a slicer technique in your final Excel dashboards.

Like I said, there are so many different Excel courses out there and you’ll be able to pick up and find some of the best trainers in the world using some of the other resources that I’ve provided in this article.

If you have any Excel questions either way, you can always ask me in the comments.

Back to Guru Categories

Become an Excel Guru by Reverse Engineering Excel Templates and Dashboards

 

This section reviews some of the cool ways to find templates and dashboards so that you can reverse engineer a spreadsheet on your own.

You can apply and use some of the techniques like show all formulas (SHIFT + `) that I showed you in the Learn in Excel section. This method of learning is definitely one of the best and I have to say it’s one of the ways I learned to apply and create dashboards in Excel.

 

16. Dissect and Learn Excel through Downloading Free Templates in Excel

 

Download MS office Templates for Excel

If you’re really not in the mood to find and pay for dashboard templates in Excel, this is one of the best ways to begin to learn some of the techniques that are utilized to build these templates.

All you’ll need to do is the following:

#1 – Go to the file area of your current spreadsheet in Excel.

#2 – Search for a template keyword of your choice (see # 1 in the screenshot).

#3 – Double click on the template of your choice.  This will open up for you in Excel and then you can start reverse engineering.

One of the things you’ll want to do is use some of the techniques that I showed you in the Learn in Excel section.  You can apply things like show all formulas, evaluate formulas, and simply investigate the formula itself using the insert function option.

As you review and reverse engineer many of these templates you’ll realize that they are using many of the same techniques like data validation, dynamic name ranges, SUMIF function and many more.

 

17. Learn Excel by Reverse Engineering a 5 Dollar Dashboard from your Industry

 

Excel 5 Dollar Dashboards

The beauty about learning and downloading a dashboard from my website is that there is a good chance (hopefully) that you’ll find something pertinent to your industry or what you’re doing.

You’ll see things like how I use shapes in Excel to build nice visual displays without taking away from the reason for the dashboard in the first place.

I have also created entire blog posts dedicated to explaining some of my secret techniques using video training as well as the written blog post itself.  These are great resources especially if you’re not willing to drop your coffee for the week on a dashboard.

Here are some free resources that you can use to learn some of the techniques used in my Excel dashboards:

#1 – Excel Customer Aged Receivables Dashboard

#2 – Excel Inventory Planning Dashboard

#3 – The Excel Human Resources Salary Dashboard

#4 – The Excel Budget and Expense Tracker

#5 – The Excel Sales Trend Dashboard

 

Back to Guru Categories

Become an Excel Guru by Acquiring Relevant Excel Skills 

 

The problem today with just going out there and trying to learn the first thing that you find about Excel is that a lot of it is useless.

You want to be focusing your efforts on Excel formulas and concepts that are going to move you forward in your career.  Not only that, on the way it’s important to pick up and find shortcuts that will help you improve and also make you more efficient in Excel.

 

18. Learn the 20% of Excel Formulas that will get you to that 80 % Excel Guru Status

 

The only Excel Formulas You Really Need

 

I’m sure most of you have heard of the 80/20 principle.  The title basically explains it.

Sohail Anwar has put together a phenomenal FREE resource / Ebook that shows you and goes over what he calls the “Money Formulas”.  Essentially he has created an Ebook around the concept that there is only a finite set of formulas that you truly need in order to be successful in your career.

The Only Excel Formulas You Really Need

Sohail has also put together a really awesome and inspirational read titled How Excel got me a 6 Figure Salary in Less Than 3 Years.  This is well worth the read if you’re looking for a kick starter.

 

19. Learn an Excel Shortcut a Day Using this Shortcut Listing from Excel Jet

 

222 Excel Keyboard Shortcuts for PC and Mac

Dave Bruns from Excel Jet has done a fantastic job putting together some of the most useful (if not all) Excel shortcuts that you should be learning on the daily.

This is a list that I refer back too often as he’s provided search capabilities as well as a free PDF download.  If you’re serious about becoming efficient in Excel this is another great place to start:

222 Excel Shortcuts for PC & Mac

20. Learn Excel By Exporting a List of Commonly Asked Excel Questions Using Keywords via WebpageFx

 

Export List of Excel Questions to Excel

Here is the thing I hunted the internet high and low for this type of tool until one day I came across this gem of a tool through the blog QuickSprout.

This tool literally reviews and searches a specific set of websites based on the category selected and returns a result set of links and information for the keyword you’ve used. You can then export that set of links into Excel to review.

Here’s how it plays out.  The first thing you’ll want to do is go to the WebpageFx – FaqFox webpage.

#1 – Type in your keyword. In our case, we’ll put in Excel Tips.

#2 – Now select category, generic.  This will bring up a list of websites that the tool will scrape.

#3 – Hit the start searching button.

#4 – Download the spreadsheet with the results.

Voila.  This is what you’ll get out of this.  A nice table of Excel Tips questions that you can use learn what people are truly asking about on the web:

FaqFox List of Results

Back to Guru Categories

 

Become an Excel Guru by Being Active in Excel Communities

 

If you want to achieve the true Excel Guru status that I keep referring to over and over, you need to get involved in different Excel communities like Reddit.

These places not only offer up and provide you with the opportunity to help your fellow Excelers, but it’ll also give you the chance to ask some of the best Excel experts out there on concepts you just don’t know how to do yet.

 

21. Go to /r/Excel on Reddit and Start Helping Other People with their Excel Problems

 

Reddit-r-excel

Using Reddit’s Excel community to boost your Excel skills is probably one of the best ways to really get amazing at Excel.  The majority of the questions and problems that are asked in the community apply to real world problems that users are trying to figure out.

This is great because you get to practice with true scenarios and you really get an idea of all of the different uses of Excel.

Get out there and start helping, learning and asking your own questions.

It’s a great place for your to ask for help as well.

 

22. Go to /r/Excel on Reddit and use the TOP, HOT & Other Hidden Gems to Learn Excel

 

Reddit Hot Top Pro Tip Sections

Alright so you really need to take advantage of the header in the r/Excel community.  Here you’ll find cool things like TOP, HOT and then a host of other cool links like Add-in, Templates, and Pro Tips.

Scroll through the lists of threads under these sections and you’ll be guaranteed to find some useful Excel juice and content.

Make sure you use the filter option that Reddit has made available for your convenience. You can find links only from the past hour all the way up to all time.

 

23. Use the Professionals on Quora to Help you Become an Excel Guru

Quora Learn Excel 

Learn the ins and outs of Quora and in no time at all you’ll be able to navigate and find some of the best Excel advice and information on the internet.

Quora allows you to select a topic like Microsoft Excel and then pin it to your read page where you can get a continuous flow and feed of Excel questions and answers.

You can of course chime in to help answer questions coming from others in the community or you can sit back and let some of the best professionals in the world answer these questions.

Be sure to browse through their Topic FAQ section for Microsoft Excel because from here you’ll find the most commonly asked questions about Excel.

Back to Guru Categories

 

 

Become an Excel Guru Using Content Aggregation Tools

 

Many of you are probably already aware and use at least one of these content aggregation tools but for those of you who don’t, they are pretty freakin’ awesome.

It’s an RSS feed on steroids.  The beauty about these aggregation tools is that each tool offers a slightly different method of organizing and handling your content.

In this section, I’ll review just how you can use two of my favorite tools to help you step up your Excel game.

 

24. Use Feedly to Create an Excel Content Collection  

 

Feedly Creating Collections

 

Feedly is an incredible content aggregation tool that lets you make what are called collections.  This is great for creating direct feeds to all of your favorite Excel websites and resources (see the screenshot above for how to create a collection).

As new content gets added to your favorite sites, the feed updates to show you all of your unread content (when you select the top level collection on the left).  Again this is great for keeping up to date with the most recent Excel news as well as tricks and tips from the best in the industry.

Signup to my Excel newsletter to get this week’s content upgrade to get a full listing of the Excel websites I have on my feed.

 

Excel Collection Feedly

 

25. Use Flipboard to Create Your Own Excel Magazines

 

 Flipboard Excel Magazines

 

So here’s the thing. I stopped using Flipboard quite some time ago (as you can see by how old my post additions are above) but honestly I just realized and remembered how amazing it really is (and I’m starting back up).

Here are some of reasons why I love Flipboard for helping you become an Excel guru.

 

#1 – You can literally create a magazine with articles from anywhere on the web.  My favorite tool is the Flipboard chrome extension. Just follow the steps below and you can create and add content to your magazines while browsing the internet.

 Flipboard Chrome Extension

#2 – You can create highly targeted and organized Excel magazines by creating new ones for each valuable topic in Excel.

#3 – Once your magazines are created, you can organize the order of the content so that it reads like a book/magazine.  This is great for building yourself full blown Excel courses and tutorials.

#4 – If you’re worried about sharing your content, you can also make your magazine private (or you can leave it public of course).

Back to Guru Categories

 

Become an Excel Guru by Using Excel Content Search Techniques

 

 

Being able to apply and use advanced search techniques to find the content that you need is becoming the way of the world.

I have created an entire blog post around this subject but I wanted to add a couple of them here because of how important it really is in your quest to becoming an Excel guru.

 

26. Use the Twitter Search Page to Find the Best Excel Content  

 

twitter search home

Twitter has an entire host of advanced search techniques that you can use in your favor for finding some of the best Excel content on the web in real time.

Head over to the Twitter Search page where you can use their advanced search tool as well as find a list of their advanced operators that will help you find the exact Excel information you’re looking for.

Finding the Excel content that you need to bring you to the next level is the next natural step to becoming an Excel guru.

 

27. Use Google Advanced Search to Help you Become Awesome in Excel

Google Advanced Search for Excel 

Hopefully you’ll be as excited as I was about finding this advanced search feature through Google.

From here you’ll be able to learn all of the operators and special filters that can be used to help you find the exact Excel concept that you’re trying to master at any given time.

Play around with all of the available search features and filters until you master it.  Once you’ve done that, you can head back over to your regular Google search and apply the custom operators to fetch you the best Excel content on the web.

Back to Guru Categories

Closing Thoughts on Becoming an Excel Guru

 

Sadly, after all of those points I am sure there are many more great methods and ways to become an Excel guru.

That being said, ultimately you need to use the method that works best for you.

As your Excel skills improve, you’ll quickly realize how often people will be calling upon your expertise and sooner than later, they will not only be calling you for your help but they will be calling you because they need you.

It looks like you’ve made it!

3 Creative Ways to Use Special Fonts to Enhance Your Excel Spreadsheets

3 Creative Ways to Use Special Fonts to Enhance Your Excel Spreadsheets

How could you use a font to enhance your Excel spreadsheets?

I know that it’s tough to believe but special fonts actually offer a ridiculous amount of possibilities when it comes to building visual displays of any nature in Excel.

The more you begin to understand Excel and how objects interact, the more use you will find of these silly fonts.

When it comes to the office, you’ll be able to take some of these tactics and apply them to your spreadsheets and I’ll guarantee you end up turning some heads (hopefully the people that truly matter).

# 1 – Build a Stoplight Feature Using the Wingding Font to Monitor a Project Status

drop down option

You can pretty much use any special Wingding character for this one but I thought it would be fitting to mimic the icon set (stoplight) features that are currently already available in Excel conditional formatting.

This is a great way to visually manage and enhance your spreadsheets for projects statuses, order statuses or pretty much any type of status you can think of in the business world.  You can follow along through the video or simply follow the basic steps below to implement this tactic:

# 1 – Use data validation to create a drop down list of available statuses.

# 2 – In the cell next to your drop down list, insert the stoplight character from the wingding insert menu.

# 3 – Use conditional formatting formula for each status to set the color of the Wingding character to match the corresponding status.

That’s it! 3 steps and you’re done.  Make sure to take a peak at the video for more details.

 

# 2 – Use “Insert Symbol” with Custom Number Formatting to Visually Enhance Your Data

heat chart

I’ve covered this topic before but it’s well worth running through it again.  Custom number formatting is one of the best ways to visually enhance your spreadsheets.

Using Unicode symbols allows you to copy and paste the symbols into your custom number formatting area so that you can have positive values be displayed with an up arrow and negative values be displayed with a down arrow (of course you can use any other Unicode symbol you’d like).  In this case I use the characters available with the Yu Gothic font.

# 1 – Select any cell on your spreadsheet outside your data set.  Go to the insert symbols in the quick access ribbon.  Find the increase/decrease arrows as show in the screen shot to the left / above into the cell (both the up and down arrow).

# 2 – Copy the symbols that were inserted into the selected cell using CTRL + C. Now select the data set that you’d like to apply the custom number formatting to (see highlighted).

# 3 – Once we’ve selected the data set (type CTRL + 1), this will open the format cells window.  Head down to custom number formatting.  Paste the symbols where necessary in your custom number formatting input box.  Adjust and follow the syntax that I’ve used in the screen shot to the left / above.

If you haven’t done a whole lot of work with custom number formatting, I’ve included a few amazing resources below that you can use to get acquainted with this feature of Excel.

 

link   MORE ON CUSTOM NUMBER FORMATTING IN EXCEL

I use custom number formatting all of the time and it’s important to truly understand its functionality to really enhance your Excel Spreadsheets.

Here is one of my favorite guides for beginners on Excel custom number formatting.  It’s great especially when you’re just starting out and it’s also something that you can continue to use as a reference as you become a seasoned user.

The Definitive Guide to Custom Number Formats in Excel by Excel Tactics 

I’ve also compiled a short list of other links that are interesting in their own way and will also show you how you can apply custom number formatting in Excel:

5 Incredible Number Formatting Tricks that Will Impress Your Boss

20 Custom Number Format Disguises from My Online Training Hub

A Technique to Quickly Develop Custom Number Formats from Chandoo

# 3 – Create a Product Review Data Chart in Excel Using the Wingding Font

star icon

Creating a product review in Excel is something that’s fun but in building one, you’ll also realize that you can use some of the same techniques in other aspects of your work or business.

You can use the REPT formula to repeat a specific character a specified numbers of times (in this case stars) based on the review provided for the associated product.  Let’s see how you can do this.

# 1 – Use data validation to create a drop down for each product with a value of 1 through 5.

# 2 – Insert and find the special Wingding star character in a cell using the insert symbol and copy the text value from the formula bar.

# 3 – Use the REPT formula to generate and create the star rating in the last column of your data chart.  Use the screenshot I’ve provided to the left / above.

link   MORE ON WINGDINGS & THE REPT FORMULA

Clearly the Wingdings font is a useful tool especially when it comes to being able to visually enhance your spreadsheets.  As your skills in Excel mature you’ll be able to think of other ways to use these crazy characters. On the other side, we also have the REPT formula.  This is a great tool for using those Wingding characters.

Let’s have a look at some slick resources that you can use to learn what you can do with this font and this formula:

Wingdings and Webdings Font Icon Sheet from the Spreadsheet Guru

Replace Radar Charts with Tables to Make Comparison Easy from Chandoo 

Dashboarding Fun: Display Smiley Faces in Your Excel Dashboard from Chandoo

Use the Playbill Font to Make Your In Cell Charts More Realistic from Chandoo

Excel Dot Plot Charts for Stock Price from Contextures

[et_bloom_inline optin_id=”optin_7″]

Already a subscriber to the ultimate Excel newsletter?
Use the password that was sent to you via email to access the content.
If you are having trouble finding it, shoot me an email.

check

TODAY’S CONTENT UPGRADE: 

Learn how to apply data validation to your Excel forms – See below.

Data Validation

How are you Going to Use these Special Fonts to Enhance Your Spreadsheets?

 

Challenge yourself right now to come up with some creative ways to implement some of these visual Excel tactics to your spreadsheets and in your work.

I honestly use these characters often in my Excel dashboards but you can also think about implementing them into timelines or even custom / self made Gantt charts (i.e. clue it has to do with using the REPT formula).

Be sure to leave a comment letting me know how and where you may have used some of these special fonts to enhance your spreadsheets.  I’m seriously looking forward to hearing from you.

The Vlookup Formula: Why Your Employer Wants you to Know How to Use It (And What You Can Do About It)

The Vlookup Formula: Why Your Employer Wants you to Know How to Use It (And What You Can Do About It)

SOLVE YOUR BUSINESS PROBLEMS USING EXCEL.

Learn how to use Excel and your data to solve your business problems and become the office hero.
SIGN ME UP

The Vlookup Formula: Why Your Employer Wants you to Know How to Use It (And What You Can Do About It)

Have you ever looked at a data set and thought, if only I had one more piece of information I could really understand the full picture and make good decisions with this information.  Realizing that you can make this happen is a pretty rewarding experience.  Mature employers realize that this is something that has the potential to truly transform their day to day operations simply by making information that isn't technically available, available.

 

Why Your Employer Wants You to Know How to Use the Vlookup Formula


 

Databases administrators often do not reflect or understand business requirements and very often when working with business intelligence software will not setup the appropriate table relationships that are required to allow reporting and business users to extract the data that they need to make their business decisions.   You may also have other external data sources that simply don't include the information that is required in one simple table (but it is available in separate data sets). This is where VLOOKUP really comes in handy and it's also why employers love that you know how to use it.
 
the Vlookup Formula
 
 

What you Can Do About It – Learning the Vlookup Formula by Example

 

I can’t even count on one hand how many times in a day I resort to using the Vlookup formula for solving questions and problems at work.  When you think about it, it’s really the best thing ever.  Being able to merge multiple sets of information into one to make a set of super information.  In my eyes the Vlookup is a tool to merge information.  I want to show you a practical real life example on how you can use the formula to get the solutions you need.

 

Vlookup - Merge Data

 

 

DOWNLOAD THE WORKBOOK FOR FREE.

 

Using the Vlookup Formula to Find the Months On Hand for Each Item Number – A Practical Example

As the Purchasing/Planning and Inventory Supervisor I need to understand what’s going on in the plant with our inventory at all times.  It’s important to optimize inventory levels and quantities to match our customer demand levels.  Too little, and we could get stuck not being able to fulfill a customer order on time.  Too much inventory and that’s just not good for anyone.  It ties up cash flow, it consumes valuable floor space, and it also consumes labor and time (this is all waste). It’s important that I always know where and what slow moving inventory I have and also what inventory I need to start restocking more of.  This allows me to understand what items I need to come up with creative solutions to reduce inventory for and it also allows me to get system tools in place to manage my faster moving items.   In order to do this, I need to pull an on hand inventory report and I also need to pull a usage report.  I typically pull the last 12 months of usage for each part (sometimes I look specifically at the last 3 or 6 months as well to see what the average usage rates are).  Once I have both sets of data, I can now use the Vlookup formula to pull in the last 12 months usage into my on hand inventory data set.  Let’s have a look at how to do this.

STEP 1: Gather Your Data and Setup Two Worksheets in Your Workbook.

Vlookup Multiple Spreadsheets

Getting the data required is the first step of the process.  Pull the data from your software for each table that is required. Once you’ve got your data, you can put each set of data on their own worksheet within your workbook.  This will allow you to keep things clean.  For this example, on one sheet I’ll place my on hand inventory and on the other I’ll place my inventory usage data.  Now that we’ve got the data organized, we can use the Vlookup formula to pull over the usage data into our inventory table for each item.

STEP 2: Use the Vlookup Formula to Merge Your Data.

Vlookup formula Screenshot

Once you understand how the Vlookup formula works, there isn’t much to it.  In the screenshot above I’m looking up the item number in column B (cell B3). The second step to this is to then highlight the column range from the column that you will be matching against (item number – Column B – on the usage table) to the column that you want to return your result from (L12 Usage Qty – Column D – on the usage table).  The third step is to select the column number that you want your value to return from on your usage table.  In this case we want the 3rd column of the highlighted area (i.e. L12 usage Qty). Finally the last step of the Vlookup formula is to tell it whether you want to find the exact match (false) or an approximate match (true).  In this case of course we want to return the exact match, which makes our value false.  As you can see above, column E of our On Hand Inventory Table now tells you the L12 months usage for each item (note: you can either copy and paste the formula in the cells or use the little square in the bottom right hand corner of the cell to fill the rest of your data set).

STEP 3: Calculating Months On Hand Using Other Formulas

Calculating Months On Hand

Now that you’ve merged the usage data into the on hand inventory table we can calculate the months on hand.  This is simply the on hand quantity/(L12 Usage Qty/12).  This will then tell us exactly how many months on hand we have for any of the items that we have inventory for.  You can then use filtering and conditional formatting to identify items that you’d like to create action items for based on a certain criteria/# of months.  As you can see being able to merge your usage data with your on hand inventory can be extremely beneficial to a business/employer.

Although the Vlookup formula can seem pretty intimidating, you can see why it is appealing to employers that the employees that they hire understand how to use this awesome formula. As you start to understand the formula you’ll realize that you can pretty much merge any type of information as long as you have a lookup key between the tables that you’re merging your information from.  There are other technicalities that you can learn about the Vlookup formula but as you become seasoned with the formula you’ll be able to pick those things up quickly.  To help you with that process, I’ve included several free / paid resources that you can use to become the employee that each business needs and wants to hire.

How and in what way do you see yourself using the Vlookup formula in your work?  Let me know in the comments below.

 

FREE RESOURCES TO TAKE YOUR VLOOKUP GAME TO THE NEXT LEVEL

VLOOKUP EXAMPLE EXPLAINED AT STARBUCKS

Excel Campus Starbucks Vlookup

THE VLOOKUP FUNCTION - AN OVERVIEW

The Vlookup Function

EXCEL VLOOKUP TUTORIAL FOR BEGINNERS

AbleBits - Excel Vlookup
SUGGESTED PRODUCT FOR HELPING YOU LEARN THE VLOOKUP FORMULA

THE VLOOKUP BOOK

the Vlookup Book

The Vlookup Book By Chandoo is an awesome resource for learning pretty much any technique you can think of when it comes to the Vlookup formula.  I am an affiliate to the product but I definitely highly recommend the read if you are serious about learning all of the different ways you can make use of the Vlookup formula to make you the office expert.

Excel Tutorial: 3 Tips on How to Create an Automated Budget & Expense Tracker

Excel Tutorial: 3 Tips on How to Create an Automated Budget & Expense Tracker

It always seems that no matter how hard you try, there is always an easier way to do something.  My wife emailed me one day this week asking if I’d be able to build her something in Excel that could handle the tracking of all of her departments expenses and then summarize and subtract the transactions from the budget total so that she could tell just exactly how much money she had left in each area.

Unfortunately in past years, she had been handling this through a word document where everything had to be done manually, but I assured her that those days were over. I built this custom budget and expense tracker so that my wife had all of the information that she needed including when and how close she was to eating up her budget.  The tracker allows you to input detail records for each type of expense incurred and summarizes and subtracts the detailed money spent within each expense type on the main budget and expense tracker dashboard.  Have a peak below at how you can enter your detailed expense records and have your total spend and budget remaining update accordingly.

TIP#1

 

TIP # 1: Keep Your Dashboard Header Visible Using Freeze Panes

Step # 1: Selecting the Row Below Where You Want to Freeze


The first step in freezing your header to make it visible is selecting the row just below the area you would like to freeze. Select the row number on the left of the worksheet and this will highlight your entire row.


Step # 2: Accessing Freeze Panes from the Quick Access Ribbon


Once your row is highlighted, you can head over to the view tab on the quick access ribbon and select freeze panes. This will bring up 3 different options but you’ll want to select freeze panes again.


Step # 3: Using the Scroll Bar to Watch the Magic – Freeze Panes!


Use the scroll bar in your worksheet to move up and down. Notice that anything below the rows that you have frozen will move but your dashboard/tracker header will remain visible. Freeze panes has to be one of my favorite options for this reason.


TIP#2

 

TIP # 2: Create Buttons in Your Header to Navigate Between Worksheets

Step # 1: Create the Shape


The first step in creating buttons in Excel is to create, select and add a shape to your worksheet.  To add a shape to your worksheet, in the Quick Access Ribbon go to the insert tab and then select shapes under the illustrations tab. This will bring up a drop down menu with multiple shapes.  From here you’ll select the shape you’d like to use as your Excel button.  This button will be used to navigate between sheets.


Step # 2: Edit the Text


Now that the shape is created, you’ll want to select the shape by right clicking and then you’ll want to select edit text. This will of course allow you to change the text.  Change the text to display the tab name that you’d like to link to in your workbook.


Step # 3: Create the Hyperlink to Link the Button to Another Tab


Now that you’ve changed the button text, you can create a hyperlink with the button so that when the user clicks on it they are brought to another worksheet (such as “Expense Entries”).  To do this, if your shape is still selected type CTRL + K or go to the insert tab and select hyperlink.  Once you are in the hyperlink window, go to place in this document.  Under the cell reference section, select the tab/worksheet within your workbook that you want to link to. NOTE: You can also link to name references, which means if you wanted to you could link to a specific cell anywhere within your workbook.


TIP#3

 

TIP # 3: Use a Nested SUM and IF Formula to Summarize Your Detailed Expenses

Step # 1: Select the Cell On Your Budget & Expense Tracker Where Total Spend Will Be Calculated


The first step is definitely an easy one.  The only thing you need to do in this step is select the top cell on your budget and Expense tracker and get prepared to create the nested SUM & IF formula.  For the purpose of teaching you this exercise, I will suggest that the expense entries and data that is being referred to is put into an Excel Table so that you can use structured references in the array formula that you will be creating.


Step # 2: Creating and Writing the Nested Array SUM & IF Formula


Now that you’ve selected the cell and setup your expense entries table, you can create your nested array SUM & IF Formula (click here for more on array formulas).  The SUM & IF Formula has one simple job, it is to return the sum of all expense entries that match the associated expense type from column B.  This means that if we are looking at cell D5 as shown below, we want to SUM the value of all expense entries when/(IF) our expense type on our expense entries table equals the expense type in D5.

As I have mentioned in step #1,  you can use structured references to build an array formula that will handle this situation. Here’s what our formula will look like:

 

SUM & IF Formula

 

The Formula Explained:

 

  • SUM is used to find to calculate the total sum of all expense entries

 =SUM(

  • IF will be used to SUM only the values in our value of purchase column (on the expense entries table) where the associated expense type in our Budget & Expense Tracker equals the same expense type (Select Purchase Type Column) on our detailed expense entries table/sheet. So if B5 equals the purchase type on our expense entries table, our true statement in the IF formula will add all of our entries when true or will add 0 for the lines that are false.
  • Once you’ve entered your formula and closed the last bracket, type CTRL + SHIFT + ENTER.  This will make your formula an array.  The reason why you want to do this is because you want the formula to not only find the first record where the expense type equals the select purchase type on your expense entry table but you want to sum an array of numbers (multiple entries) where this statement/formula is true.

 

{=SUM(IF(B5=ExpenseEntries[Select Purchase Type),ExpenseEntries[Value of Purchase ($)],0))}


Step # 3: Watch Your Budget & Expense Tracker Update as you Add Expense Entries


Now that you’ve created the formula, you can copy that formula down across all expense types on your budget and expense tracker.  After that’s completed, you can head over to your expense entries table and add records.  You’ll notice that your budget and expense tracker automatically updates to show total spend to date as you add new detailed expense records to your table.


The Budget & Expense Tracker Review

I love simplifying and making processes and tasks easier.  Excel is the perfect tool for doing this.  In this week’s post I was able to go over 3 different tips that you can use when building your own dashboard or tracker.  If you feel that this tracker would be useful for your own purposes, feel free to purchase a copy of the tracker below.

 

If you were to build your own budget and expense tracker, what would you make sure to include and how would you do it?

4 Easy Steps to Using the Excel Option button for Dynamic Dashboards

4 Easy Steps to Using the Excel Option button for Dynamic Dashboards

In business, I often see disputes between the finance and operations departments on which metric is a more important indicator of performance.  Operational leaders (more often than not) like to see their numbers in terms of units/quantities and the finance executives/leaders want to see everything in dollars or currency.  This is where the Excel option button form control comes in handy (note that if you are part of a finance team, you can also use this logic to switch between local currency versus your company’s global standard currency).  I’m going to show you how you can use the option button to switch between quantity/units and currency all in one graph.

(more…)

Excel Formulas Not Working Part I: Understanding Relative, Mixed and Absolute Referencing

Excel Formulas Not Working Part I: Understanding Relative, Mixed and Absolute Referencing

Rss_feed_sub

I know what you’re thinking.  The title of this post is a sad attempt at stringing together an English phrase that actually makes sense (i.e. Excel Formulas Not Working).  To that I say, you’re absolutely right!  Nevertheless, it seems that the majority of people out there are searching for this exact statement on Google.  For that reason, I am going to leave it and I’m also going to try to put together a comprehensive series of blog posts that can answer this question – or this statement rather. We’ve all come across the dreaded situation.  We put together a massive spreadsheet and start looking at the results of our calculations and formulas only to realize that the results are incorrect.  Now you’re thinking, well what is the problem?  In this first of several posts we are going to look at what Tech Republic says is the most important thing to teach your Excel users and that is absolute and relative cell referencing.  This post will give you a solid idea of why your formulas may be failing or may just be not giving you the results you expected and at the same time cover the basics of absolute and relative cell referencing so you can understand what the heck you’re doing.

 

Relative, Mixed and Absolute Cell Referencing: The Reason Why Your Excel Formula Didn’t Auto Fill or Copy As You Expected

I know sounds brutally boring, right? It kind of is really; but if you understand how this works, you’ll save yourself a lot of time trying to figure out why your formulas aren’t producing the results you were expecting.  This is one of the first things you want to learn about Excel before you start using formulas as it will save you tons of time in the future.  Alright let’s get into the less than exciting details and then look at the fun stuff.

what_is_a_cell

What Exactly Does Cell Referencing Even Mean?

Literally and in the most laymen terms cell referencing means to reference a cell from your current cell. This allows you to make the contents of a cell dynamic meaning if someone updates cell A1 and you are referring to that cell in cell C5, cell C5 will update to reflect the changes made to A1.  Let’s look at an example and check out the step by step process.

 

cell_referencing_static

A STEP BY STEP HOW TO ON CELL REFERENCING

(1) Notice that our formula in cell F3 is (=D3*E3 – here we are referencing cell D3 and E3) – which simply translates to multiply the contents of D3 by E3 (225 * $68.60).  The result in cell F3 is then $15, 435.67.

(2) The beautiful thing about cell referencing is that now if we change the value of one of the cells being referenced (let’s say the on hand quantity- column D), this will change the result in cell F3 automatically.  Notice that the value in cell D3 is currently 225.

(3) Let’s change the value of cell D3 to 200 – notice the red circle for point 3 that the value has now changed.

(4) Now that we’ve updated cell D3, because the formula in F3 remains the same (D3*E3) our result will be dynamic and change to reflect the new multiplication of values between D3 and E3 (=200*$68.60) which gives us $13, 720.59.

That is cell referencing in a nutshell.  Instead of doing a multiplication of two values and re-populating cell F3 every time based on the changes that are made for cell D3 and E3, we can easily refer to those two cells so that we don’t need to manually calculate the change in our inventory value.

Alright, now that we’ve covered cell referencing in general, let’s look at the different types.  We’ve got relative, mixed and absolute cell referencing.

 

Relative Cell Referencing

By default your formulas use relative cell referencing (it’s important that you understand that this is the default setup in Excel and that there will be times where you will need to make your formula not relative).  What this means is that if you copy your formulas to other cells the references will simply be offset exactly by the same number of rows and columns that you moved your formula (Microsoft did a great job at explaining this here).

 

Relative_Referencing_Gif

 

BREAKDOWN OF RELATIVE REFERENCING

(1) Enter your formula in cell F3 (=D3*E3).

(2) Hit Enter to process the formula.

(3) Use small square in bottom right hand corner of cell F3 to copy/auto fill formula to next cell (drag).

(4) Now that you are on cell F4 – the formula bar shows that the formula was copied over.

(5) Notice that the column and the row of each cell reference is offset exactly by the number of cells that we moved and copied the formula (F3 to F4 – 1 row down to row 4 – D4 * E4).

 

Mixed Cell Referencing

When we are talking about mixed cell referencing, here we are referring to both relative and absolute referencing in one formula/cell.  This means that a part of the formula will always be referencing a specific range or a cell when copied or moved (absolute referencing) and part of the formula will be referencing a dynamic or a changing range or cell (relative referencing).  As an example, as you copy and paste your formula,  you may want to always refer to column A but at the same time want to make sure that you change rows as you paste your formula to new cells.  In order to do that, you are going to place a dollar sign in front of the column character that you are dealing with – $A1.  Let’s have a look.

 

F4_Key_Display_Quick_Tip

F4 - Absolute Versus Relative Referencing CLICK IMAGE TO SEE ANIMATED GIF

 

mixed_referencing_gif_1

BREAKDOWN OF MIXED REFERENCING

(1) Enter your formula in cell F3 (=$D3*$E3) – the dollar sign before the column of each reference means we want it to stay the same.  So we will continue to reference column D and E when the formula is copied but the row reference will change as we move down a row.

(2) Type Ctrl + C (in windows) to copy the formula from F3.

(3) Paste the formula into cell G4.  The reason that I changed columns is because I wanted to show you that even though we moved over a column (offset of 1 column), we continued to refer to column D and E in our formula – but notice that our row offset itself accordingly (new formula in G4 = $D4*$E4). 

Note: If we had not put the $ in front of D and E, our new formula in G4 would have been =E4*F4 giving us an entirely different and unexpected answer.

 

 Absolute Cell Referencing

Absolute cell referencing is the complete opposite of relative cell referencing.  When we develop a formula and add the $ symbol before both the column and row reference, the formula is said to be absolute.  This means that no matter what, when we copy the formula from one cell to the next, we are always going to reference the exact same cells and ranges that were initially outlined in the originating cell.  For example cell F3=$D$3*$E$3 – if we copy this to cell G4, G4 will equal the exact same thing (=$D$3*$E$3).  This ultimately means that our result will be the exact same for F3 and G4.  A good example of when to use this is if we have a  storage fee per cubic feet in a cell and we want to calculate the storage fees based on the total cubic feet used of each item (I’m just sticking with inventory theme here to keep things consistent).  Let’s take a look at this example.

 

Absolute_referencing_gif

 

BREAKDOWN OF ABSOLUTE REFERENCING

(1) Enter your formula in cell H3(=G3*$M$2) – the dollar sign before the column M and row 2 means we are going to refer to this specific cell when the formula is copied.  Because our first cell reference G3 has no absolute reference on row or column, when we move down only one row, we will stay in the same column but change rows (offset down 1 row) hence now referencing G4.

(2) Copy the data down the column by double clicking the small square in the bottom right hand corner of H3.

(3) Double check cell H4 and you’ll notice that we are still referencing cell $M$2 but we’ve changed our first value/cell reference to G4.

 

Practical Examples: Situations and Solutions to Excel Formulas Not Working as a Result of Cell Referencing

To really try and drive this concept home, we are going to go over a few different examples so that we can see what happens when we don’t apply the proper referencing syntax.  This will hopefully teach you enough so that you no longer have to be worried about your formulas not working as a result of cell referencing.

 

Don’t be Lazy – When Not to Use Relative Referencing or When to Pick up the Slack and Start Using those $ $igns

There comes a time as an Excel user where you need to step away from just using the standard Excel default cell referencing.  When the time comes you’ll need to make sure to use those dollar signs.  As you can see in our example below, we forgot to make the reference to cell M2 absolute.  As a result, because we copied our cell one row down (the new cell column remained the same as the previous), we moved our reference one row down as well to M3.

 

absolute_neglected_gif

The Solution:  Before you copy the contents of H3 over to new cells, be sure to make the storage fee an absolute reference.  In this case our formula in H3 should then be as follows (H3=G3*$M$2).  You should know this by now but if you are still asking why does G3 not have any dollar signs, it’s because we know that as we move to H4, we also want to move the reference to G4 (as the row changes in the cell that contains the formula, we want the row to change on the reference for the cubic feet column/field).  Technically, we should make G3 have an absolute column of $G but because we are not copying the formula to another column we can get away with this.

You’re Not Making Any Money With Those Dollar Signs – When Not to Use Absolute Referencing

Alright so it’s one thing to forget to use the absolute reference (using dollar signs) but it’s also important to know when not to use them.  In this scenario we are going to look at when and why it’s important not to make your cell references absolute.  In the image below, you’ll notice that when we make our formula in F4 absolute, and we drag that formula to F11, every cell from F5 to F11 calculates $D$3*$E$3 instead of changing rows to multiply the Qty On Hand by the Unit Price for each record.

 

absolutely_wrong

The Solution:  Again, it’s important to look at what you are looking for in terms of a result and this will determine what you need to do for the referencing.  In our case, we’ve made both of the cell references in F3 absolute (again the dollar sign before a row/column reference makes it absolute) and we’ve copied that formula by double clicking the small box in the bottom right hand corner of the cell (TIP: this will fill your current column down as far as data exists to the right or left columns).  Because both the column and row references were absolute, our formula remained the exact same multiplying D3 by E3 for each of the cells.  To fix this, we need to remove the absolute referencing by selecting each individual reference in F3 and hitting F4 until no dollar signs exist in our equation.  This will of course make our formula relative (REMINDER: relative referencing is the default) which will allow us to copy this formula down and ensure that we multiply QTY ON HAND by UNIT PRICE for each row/record in our data set.  It’s important to note that we don’t need to make the column reference absolute here because we are not copying the formula into a cell that is outside our current column.  This is because relative to our original cell, we have not moved over to the left or right a column – the next part of this section should explain this in detail with a screen shot.

We Can’t Always Operate on a Hybrid – When You Need to and When You Don’t Need to Use Mixed Referencing

In this example I’m going to show you a situation where you need to use mixed referencing to ensure you get the result that you are looking for – this is the same example as above but bare with me.  This is kind of an uncommon situation but let’s pretend like we had two columns for Total On Hand Value (OHV2 being the second column).  In this column, we want to ensure that we have the same result as we have in F4 which is by the way (a relative reference to D4*E4).  If we were to copy the relative formula from F3 (D3*E3) to G4, the formula in G4 would be E4*F4 which would not give us the total on hand value as we expected (this is because we moved 1 row down and 1 column right – as opposed to just 1 row down which was the case in cell F4).  Let’s look at how this works:

F3= D3*E3 ——Copied to G4 ——–G4=E4*F4  (G4 is located exactly 1 row down and 1 column right of cell F3)

  • D3 therefore has to move 1 row down and 1 column right when the reference is relative.  That means G4 will reference E4.

1 row down-1 column right

  • E3 will also move 1 row down and 1 column right when the reference is relative.  That means G4 will reference F4.
  • The final formula in G4 therefore becomes E4*F4.

Using a relative reference in F3 and copying it to cell F4 would work.  This is a situation where mixed referencing is not required because we are copying our cell formula and pasting it in the same column (F4 is 1 row down from F3 and no columns to the right or left which means that each of our references in our formula will also only move 1 row down when copied).

The Solution:  To make sure we get this right (so G4 is the result of D4 multiplied by E4), we need to use a mixed reference in F3 so that we make our columns absolute (rows will remain relative) which will then allow us to copy the formula to G4 and get the result we are looking for (G4=$D4*$E4).  Below provides the example of the correct answer/result.

Make sure you have a look at the table below our data set.  This outlines each reference method iteration that we can have in F3 and how it will effect the result if it is copied to G4.  The table also explains how the formula works (formula instruction column) for each iteration.  You may want to keep this screen shot as a quick reference to how cell referencing works.

cell referencing large image

Summary and Closing Thoughts on Excel Formulas Not Working and Cell Referencing

I think that about covers all of the bases to make sure that when you are using formulas that you are also using the correct referencing method.  The biggest thing to keep in mind is how you would like your formula to be copied over and what cells you’ll want your formula to reference in the cell that you are pasting to.  If you understand and apply these concepts, you’ll be sure to rule out your Excel formula not working as a result of incorrect cell referencing.

If you enjoyed this post and found it useful, please do share with your peeps!  Also, I don’t often get many comments but I’d love to hear your thoughts on what other problems you experience when it comes to formulas not working so PLEASE DO LEAVE A COMMENT!!!

I’m looking forward to adding other articles to this Excel Formula Not Working series and I hope you are too.

Until the next time!