101 of the Most Shared Excel Articles You Need to Read

101 of the Most Shared Excel Articles You Need to Read

Are you overwhelmed by the amount of Excel articles on the web?

If you said yes, then we are definitely on the same page and I’m willing to bet there’s a few or many following behind us.

Over the past couple of weeks I’ve set out to find the most shared Excel articles on the web and I’ve decided to put together a list showing you only articles that have been shared 100 or more times.

I’ve created this list using an amazing tool called Buzz Sumo which allows you to find the most shared content on the web for keywords, websites, videos, podcasts you name it.

The whole reason why I started this little adventure is because I want to provide you with some of the most valuable information you can find on the web when it comes to Excel and I think I’ve achieved this here.

As you go through this list, I’ll provide you with the name of the article and a link as well as the number total shares sorted from highest to lowest.

 

GET THE SORTABLE SPREADSHEET BY SIGNING UP TO MY NEWSLETTER BELOW.

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: 

Get a Sortable Spreadsheet with 101 Excel Articles that Includes Links,# of Shares by Social media Type, Excel Category, Website and Rank.

101 Of the Most Shared Excel Articles

 

TOP 50 EXCEL ARTICLES

[1] 12 Excel Formulas, Features & Keyboard Shortcuts Everyone Should Know  [Total Shares: 10,053]

[2] 7 Handy Excel Tricks That’ll Impress Your Boss – Infographic [Total Shares: 9,658]

[3] 5 Tips to Get the Most Out of Microsoft Excel Office [Total Shares: 3,364]

[4] Eight Useful Microsoft Excel Shortcuts Worth Memorizing [Total Shares:  3,183]

[5] 11 Excel Tricks to Teach Your Students [Total Shares:  3,022]

[6] Why Pivot Tables? [Total Shares:  2,609]

[7] 300 Excel Examples [Total Shares:  1,807]

[8] 40 Handy Excel Shortcuts You Can’t Live Without [Total Shares:  1,701]

[9] Learn Eight Helpful Microsoft Excel Tricks with This Handy Cheat Sheet [Total Shares: 1,541]

[10] Excel logical formulas: 5 simple IF statements to get started [Total Shares: 1,475]  

[11] Awesome Excel Tricks to Impress Your Boss [Total Shares: 1,373]

[12] 6 new Excel functions that simplify your formula editing experience – Office Blogs [Total Shares: 1,314]

[13] How to Master Microsoft Office Excel [Total Shares: 1,218]

[14] Your Excel formulas cheat sheet: 15 tips for calculations and common tasks [Total Shares:  1,196]

[15] Microsoft Excel – Working With INDEX() Formula [Total Shares: 1,081]

[16] Simple But Powerful Excel Tricks for Analyzing Data [Total Shares:  1,039]

[17] MICROSOFT EXCEL – VLOOKUP [Total Shares: 1,028]

[18] How to analyze business data in Excel [Total Shares: 1,012]

[19] Work Faster in Microsoft Excel: 5 Secret Tricks [Total Shares: 1,001]

[20] 3 ways to drive business decisions using the new Excel 2016 charts – Office Blogs [Total Shares: 966]

[21] Microsoft excel formulas list with examples [Total Shares: 885]

[22] Tricks of Text to Column in MS Excel [Total Shares: 781]

[23] Top 15 Useful Excel Formula Cheat Sheet [Total Shares: 720]

[24] Excel: Cash Flow Waterfall Charts in Excel 2016 – Strategic Finance [Total Shares: 674]

[25] Making Cash flow summary in Excel using Pivot tables with data on multiple worksheets [Total Shares: 621]

[26] 8 Great Tools to Make You an Excel Expert [Total Shares: 580]

[27] Working with Data and Date Related formulas in Excel [Total Shares: 562]

[28] Making Profit and Loss Statements in Excel using Pivot tables [Total Shares: 537]

[29] 8 tips and tricks you should know for Excel 2016 for Mac [Total Shares: 530]

[30] How to Add a Secondary Axis to an Excel Chart [Total Shares: 491]

[31] 5 essential tips for creating Excel macros [Total Shares: 488]

[32] Gantt Chart Excel: Step-by-step, visual tutorial [Total Shares: 467]

[33] Better Profit and Loss Statements with Waterfall Charts in Excel [Total Shares: 455]

[34] What to do with Excel 2016’s new chart styles: Treemap, Sunburst, and Box & Whisker [Total Shares: 453]

[35] 5 Little-Known Excel Tips That Will Make You Look Smarter Than Your Co-Workers [Total Shares: 437]

[36] FIFO Inventory Valuation in Excel using Data Tables [Total Shares: 436]

[37] 001: VBA & Excel Add-Ins with Chris Newman [Total Shares: 415]

[38] Key Tips in Excel [Total Shares: 412]

[39] Use INDEX and MATCH for simple database queries in Excel [Total Shares: 393]

[40] Top Excel Tips For Data Analysts [Total Shares: 391]

[41] Learn how to unpivot static tables in Excel 2016 – Office Blogs [Total Shares: 387]

[42] Use Conditional Formatting to make Charts for Excel dashboards [Total Shares: 376]

[43] Five Excel Tips You Will Actually Use [Total Shares: 366]

[44] 27+ Ways to Teach Yourself How to Become the Excel Guru Everybody Relies On [Total Shares: 359]

[45] Excel tips for young CPAs [Total Shares: 355]

[46] Multi-level Pivot Table in Excel [Total Shares: 353]

[47] Calculate Years, Months, Days elapsed from a certain date in Microsoft Excel 2010 [Total Shares: 333]

[48] Excel Tip: Bars, Scales, and Sets [Total Shares: 317]

[49] Learn how to shape and transform data with unprecedented ease in Excel 2016 – Office Blogs [Total Shares: 303]

[50] How to Create a Checklist using Conditional Formatting in Microsoft Excel [Total Shares: 269]

 

 

TOP 50-101 Excel Articles

[51] The Zen of What-if Analysis  [Total Shares: 249]

[52] Excel Tips Format Time [Total Shares: 235]

[53] Formula Auditing in Excel [Total Shares: 232]

[54] 21 things you should know about VLOOKUP [Total Shares: 223]

[55] The Great Circular Referencing Hoax in Excel [Total Shares: 215]

[56] Dynamically Highlight data points in Excel charts using Form Controls [Total Shares: 211]

[57] Top 10 Excel Productivity Tips [Total Shares: 209]

[58] 15 Quick and Powerful ways to Analyze Business Data [Total Shares: 207]

[59] Transpose Data in Excel [Total Shares: 204]

[60] Multi Conditional Vlookup in Excel [Total Shares: 200]

[61] Transitioning from Excel Power Pivot/Query to Power BI Desktop: A Remarkably Smooth Road [Total Shares: 199]

[62] How to delete an entire row based on a condition? [Total Shares: 195]

[63] Separate Values and Text in Power Query – Part 2 [Total Shares: 194]

[64] Excel Flash Fill [Total Shares: 192]

[65] 200+ Excel Formula Examples [Total Shares: 191]

[66] Pivot Table Grouping – Numbers & Dates [Total Shares: 185]

[67] Guidelines for Creating Effective Dashboards [Total Shares: 183]

[68] Create Custom Filters 4X faster using Excel VBA [Total Shares: 180]

[69] Structured Referencing to Identify Parts of Excel Tables [Total Shares: 174]

[70] VLookup To The Left WithThe Choose Function [Total Shares: 172]

[71] Apply Custom Data Labels to Charted Points [Total Shares: 171]

[72] 002: Power Query with Ken Puls [Total Shares: 167]

[73] Excel Sparklines [Total Shares: 161]

[74] KPI Dashboard in Excel – Dynamic Chart Interpretation [Total Shares: 161]

[75] Auto highlighting Excel charts [Total Shares: 156]

[76] Power Query/Excel 2016 VBA Examples [Total Shares: 153]

[77] Bullet Charts in Excel (updated and simplified) [Total Shares: 151]

[78] 10 Little Known Facts about Excel’s Humble Beginnings [Total Shares: 149]

[79] Excel PivotTable Calculated Items by Position [Total Shares: 148 ]

[80] CP050: Fifty Excel Tips to make you awesome [Total Shares: 142]

[81] Automatically Generating Date Dimension Tables In Excel 2016 Power Pivot [Total Shares: 138]

[82] 3 Reasons Why You Need to Start Using Excel GETPIVOTDATA  [Total Shares: 137]

[83] Merge Tables using Inner and Anti Joins in Power Query [Total Shares: 137]

[84] 5 Easy Steps on How to Make an Excel Dashboard [Total Shaes: 135]

[85] Excel Slicers – Introduction, what are they, how to use them, tips, advanced concepts, interactive charts & reports using Slicers & Pivot Tables [Total Shares: 132]

[86] Excel Multi-cell Array Formulas [Total Shares: 130]

[87] Custom number formats in Excel [Total Shares: 128]

[88] Excel Slicer Trick  [Total Shares: 127]

[89] Excel Custom Number Formatting Basics [Total Shares: 127]

[90] Calculate Hours Worked in Excel with Power Query [Total Shares: 126]

[91] Excel Formulas: Understanding Number Formats [Total Shares: 126]

[92] KPI performance charts & dashboards – 43 alternatives (contest entries) [Total Shares: 125]

[93] VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User [Total Shares: 118]

[94] Creating a custom calendar in Power Query [Total Shares: 115]

[95] Easy Step-By-Step Instructions To Create Your First Excel Ribbon Add-in [Total Shares: 114]

[96] Charts That Update Automatically in Excel [Total Shares: 114]

[97] Finding All Selected Items In A Slicer In Excel 2016 Using TextJoin() [Total Shares: 112]

[98] 7 Excel formula errors that make you look dumb [Total Shares: 111]

[99] Identify Duplicates Using Power Query [Total Shares: 104]

[100] Creating Dynamic Chart Titles in Excel [Total Shares: 101] 

[101] Salary Chart: Plot Markers on Floating Bars [Total Shares: 100]

 

 

Your Favorite Excel Articles & Closing Thoughts

 

Building yourself a spreadsheet with links and including Excel categories will help you stay organized and really give you a solid go to resource to refer to time and time again when you’re stuck (signup to my newsletter to get your spreadsheet started).

Hopefully there are a few hidden gems in this list that you can use to skyrocket your Excel learning.

Feel free to leave any of your personal favorites below (even if they have less than 100 shares) and also be sure to leave a comment if you have any.

Until the next time.

 

 

 

 

8 Search Techniques You Can Use Right Now to Find the Best Excel Content On The Internet

8 Search Techniques You Can Use Right Now to Find the Best Excel Content On The Internet

You spend a lot of time navigating and searching the internet trying to find high quality Excel content.

I know because I’ve been there and it always seems like it takes longer than it really should to find the exact feature, formula or solution to your Excel problems or questions. Trying to find specific Excel skills that you’re interested in learning can become quite a feat.

I know and you know that there are a lot of great Excel websites out there with tons of information but sometimes it just comes down to getting your searches just right.

In this article I will be reviewing a list of advanced search techniques that you can use to find the best Excel content on the Internet (whether it be through Google or other channels).

#1 – A Quick Way to Find Some of the Best Excel Websites on the Internet (Google)

The first search technique we are going to look at is of course from Google. This technique is definitely really cool and is a great way to setup and find websites that you’d like to add to your RSS feed for continuous learning. The technique is simple. You use the term “related” followed by a colon, and then you simply include the website address for which you’d like to find similar sites.

related chandoo org

Checkout the results you get to the left. You get a gold mine of Excel websites that are similar to Chandoo’s. You can do this with the bigger players and websites that have been around for a while or that have higher page ranking.  Keep in mind this may not find results for smaller websites that have not yet reached Google rank maturity.

#2 – Use the Similar Drop Down Option When You Find Useful Excel Articles (Google)

Google has clearly mastered their game when it comes to online search.  In this search tip let’s say you find an amazing article on a particular Excel subject such as “Excel Dashboard Techniques”. If you’d like to find more articles just like it, you can learn and become an expert on the topic by clicking the drop down beside the result and selecting similar.

Similar Drop Down - Result

Once you’ve selected similar, a new list of search results that are similar to the current result will show up and you’ll have the chance to read more on the subject (in our case – Excel dashboard techniques). This search functionality is especially useful when you are on the brink of learning a specific Excel technique but you need to learn just a little bit more.  Try it out now.

#3 – Use the Google Filetype Lookup to Find Great Free Excel Spreadsheets Like Dashboards (Google)

Another gem of a search technique from Google is the ability to quickly find file types directly from the search engine by simply typing in filetype, colon, the file type name (i.e. in our case XLS) and then type a related search term for a file you’d be looking for.  Here’s an example I used to find free Excel dashboard files using the Google search bar:

filetype google search bar

Once you hit enter you’ll get a list of results, each search result will have a file type in square brackets of [xls] to the left of the result. This is a great way to find Excel sample data sets, Excel dashboards, templates etc… as well as just interesting spreadsheets that you can use to further extend your Excel skills by playing around with the features or the data that you’ve found.   Here is an example of what I found using the query above (filetype:xls dashboard).  This result was directly from the Excel Dashboard Templates website.

The awesome thing about this is the file actually has instructions and provides you with the ability to try things out on your own.

Excel Dashboard Template

#4 – Use Google Site Lookup to Find Specific Excel Content On High Ranking Excel Websites (Google)

Clearly I enjoy looking things up on Google, but man do they ever just make things easy.  Using the site lookup option, you can easily lookup Excel specific content and return results from a specific website.

This is great when looking at Excel super sites like Chandoo’s where there are thousands upon thousands of pages of Excel content.  When using this search feature, I like to put the keyword in quotations (note: quotations will lookup the exact keyword phrase – more information on Google operators).

excel slicer search query

Once you hit enter you’ll get a list of results and you’ll be shown content that is specific to the keyword you’ve used within the site you’ve requested.  In this case I wanted to find content specific to slicers in Excel coming from chandoo.org. You’ll notice in the screen shot to the left / above that all of the results are related to excel slicers coming only from that specific website.

I’m sure that you can think of more than one useful way to use this technique to help you with your Excel learning.

#5 – Use Twitter Advanced Search to Find Specific Excel Content (Twitter)

TwitterIcon

Alright I know that I haven’t covered every single search technique with Google but I think I’ve provided you with the tools to figure it out. Because I’ve covered a lot of the different operators via Google, it won’t take you long to learn the ropes with Twitter search.  Twitter has a search page that you’ll want to use to get yourself acquainted so you can become an Excel search master using their software.

Here’s the step by step process that you can follow along with using the screen shot to the left / above:

#1 – Get yourself over to http://twitter.com/search-home.

#2 – Alright now that you’re there, you can learn everything you need to know about operators (much like Google by the way) and you can also used their advanced search section so that you can master your search techniques for Twitter.

#3 – The best part now is you can merge what you’ve learned with operators and apply them to the search bar no matter where you are on Twitter.

Okay, now that I’ve shown you the techniques, let’s go through a quick Excel search example using the twitter search bar.

twitter search bar excel

 

The results will yield you a list of links where the tweet contains the exact phrase “excel slicers”.  Oddly enough it looks like someone has shared some of my YouTube content:

operator twitter search Excel

#6 – Use BuzzSumo to Find the Most Shared Excel Content by Keyword (BuzzSumo)

spearker excel

If you’re not a part of the marketing world, there could be a chance that you haven’t heard of this one yet.  BuzzSumo is great for marketers especially when it comes to finding content to curate and build round up posts around.

That being said, for us Excel learners, its best use is in being able to find the best content on the web for specific Excel keywords and phrases.

If you are going to use it, I would suggest you sign up.  Once you sign up, you will be able to perform searches.  Keep in mind that in order to get all of the functionality offered by the software, you’ll need to sign up to the pro version unfortunately.

For your purposes however, using the free version while signed up should get you exactly what you need.  For me at least I want to find the best content for certain keywords like “Excel shortcuts”.

Below is the highest rated content for the keyword “Excel shortcuts” and if you’re as interested as I am you can check it out here – 12 Excel Formulas, Features & Keyboard Shortcuts Everyone Should Know.

Highest shared Excel shortcut Content

 

#7 – Use the Excel Subreddit to Search for Excel Keywords and Topics (Reddit)

The Excel Subreddit contains a ridiculous amount of content that likely contains the results / solutions to all of your Excel questions, concerns or learning requirements for that matter.

If you are curious about anything related to Excel you can use the search functionality that is available in the top right hand corner (or mid screen after you’ve performed a search) of the Excel Subreddit screen and you are likely to find what you are looking for.  If not, the beautiful thing about the Excel Subreddit is that you can post any topic or Excel question and it will be answered almost instantaneously.

If you want to find great tips or the best content / posts made on the Subreddit, you can also use the pro tip sections as well as the top posts section.

Excel subreddit pro tip

excel subreddit top posts

#8 – Use and Search the Top Excel Forums on the Web for Amazing Content (Excel Forums)

Excel Forum

Excel forums are a little bit like Reddit but they are still a stellar tool for searching for as well as getting an answer to one of your Excel questions.

I am going to suggest 3 different Excel forum resources that you can use to either ask questions or simply search the directories for cool Excel stuff:

#1 – Chandoo’s Excel Forum.

#2 – Mr. Excel’s Excel Forum.

#3 – The Ozgrid Excel Forum.

You can use these resources in a couple of different ways.

The first would be by using the search bars (or use the site search operator with Google – see above) within the forum. The second would be to simply navigate and the third option would be to ask and post your Excel questions to have them answered by the community.

A Summary of Excel Content Search Techniques

 

As you already know, the internet is stuffed with pages upon pages of Excel content (and of course any content you can possible think of).

In order to maximize and find the most useful Excel content on the web, you really need to understand the search techniques that are available to you. In this post I’ve reviewed with you several different techniques that you can use find the best content out there.

I think it’s a great idea to practice, learn and use all of these techniques as well as the operators that are provided by each search type so that you can become a master at finding amazing Excel content.

What search techniques do you use that I might have missed? Make sure to leave a comment letting me know.

More Links and Resources to Step Up Your Excel Game

Be More Valuable in the Office: 5 Ways to Increase Your Efficiency in Excel

Be More Valuable in the Office: 5 Ways to Increase Your Efficiency in Excel

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. (more…)

Three 60 Second Excel Custom Number Formatting Videos that will Help you get Noticed in the Office

Three 60 Second Excel Custom Number Formatting Videos that will Help you get Noticed in the Office

Last week I had the opportunity to put a little bit of time into building a few videos on my YouTube Channel that focus on how to effectively use Excel custom number formatting.  Let’s be honest, when I put it that way, it sounds a little bit lame and boring.  I can assure you however that there are a few tricks in here that will likely get you excited about going to work tomorrow to show it off to your boss. Let’s dive right into the videos that are going to make your workweek a little more bearable by making you look like an Excel guru.

(more…)

3 Smart and Simple Tab Shortcuts To Make Your Life Easier

3 Smart and Simple Tab Shortcuts To Make Your Life Easier

As humans, we are always looking for smarter, better and quicker ways of doing things – especially the repetitive and often annoying day to day tasks.  There are days where we quite literally wish we had a monkey by our sides to perform these tasks, allowing us to spend our time doing things that are of actual value.  Thankfully Excel offers tab shortcuts that help us easily navigate between workbooks, change the name of workbooks at the drop of a hat and enter formulas, formatting and other Excel goodies onto multiple sheets all at the same time – can you believe it!  In this post we are going to look at 3 smart and simple tab shortcuts to make our lives easier and really, just make our day way better. (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!