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.

 

 

 

 

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.

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!

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

5 Little-Known Excel Tips That Will Make You Look Smarter Than Your Co-Workers

5 Little-Known Excel Tips That Will Make You Look Smarter Than Your Co-Workers

In this day and age, the internet is literally bursting at the seams with new content and it’s becoming easier and easier to find unique content that can help you further your career.

There are so many different Excel tips out there but today I wanted to bring up a few that I think will make you truly stand out and well, make you look smarter than your co-workers. If you are here and reading this, that tells me that you’re already more motivated and eager than the rest.

I’ve become obsessed with learning new Excel skills and tips and I’m hoping that this will get you started – new year, new you right! Let’s do this.

DO YOU WANT FREE ACCESS TO ALL MY CONTENT UPGRADES? SIGN UP NOW!

Start today and you’ll get access to today’s XLS worksheet download and of course you’ll have access to any other exclusive content I post in the future.

Have you already subscribed for exclusive content?

Click here and use the password I sent you when you subscribed.

Excel Tip # 1: Learn How to Create Custom Fill Sequences/Lists
STEP1 The first thing you want to do is get access to the edit custom lists section of the Excel options screen.  To do this follow these steps.

  • Go to the File in the top left hand corner of your Excel screen.
  • Next, select Options.
  • You’ll then need to select the Advanced section of the Excel Options.
  • Scroll down to the General section of the Advanced section and then click on Edit Custom Lists.

You’ve made it!

STEP # 2 The second step is to either create a manual list by adding items into the list entries section or you can use the import list option by selecting a range of cells and then importing the list.  Once the list is imported or you’ve added a manually created list, you’re basically ready to start using these lists with your fill handle in Excel.
STEP # 3 The third and final step is an easy one.  You can grab and use the fill handle in the bottom right hand corner of your cell (starting with the first item on your custom fill list) and then drag that down with your mouse.  You’ll notice that the fill sequence mimics exactly the list that you setup using the Edit Custom Lists screen in step # 2.

link

 

MORE ON THE EXCEL FILL TOOL AND OTHER EXCEL TIPS

 

This tip was one I picked up from Bill Jelen & Szilvia Juhasz newest book called:

MrExcel XL: The 40 Greatest Excel Tips of All Time

This book is absolutely amazing and is jam packed with the best Excel tips out there (as the name suggests).  If you're interested in seeing and checking out a review from an Excel MVP, checkout Jon Acampora's review of the book. Unfortunatley, the contest that Jon held is now over but he does have some cool pictures/shots of the book and goes through what is included in detail.

 

Excel Tip # 2: Learn How to Use the Excel Name Box

STEP1Although I’ve only technically written here that there is one step to this Excel tip, I’m going to cover all 6 tips on how you can use the Excel name box in one section (i.e this one). Here they are:

  • In the Excel name box, type in 3:3 (the row numbers) to highlight row 3.  You can highlight any given row by simply separating your row numbers by a colon.
  • In the Excel name box, type in C:C (the column numbers) to highlight column C.  You can highlight any given column by simply separating your column letters by a colon.
  • If you want to highlight multiple columns and rows at once, you can do that by selecting the range of rows then placing a comma and then the range of columns (e.g. in the name box type 3:4,C:D).
  • If you want to select multiple cells at a time, you can type in individual cell or range of cells and separate other ranges by commas (e.g. C5:D8,J8:J15).
  • Creating a name range is also very simple.  Select a range of cells that you would like to name, and then type the name in the Excel name box.  You can then use the newly created name range in your formulas.  In the video, I have a range of cells called Sales2015.  I can then perform the SUM of that name range to find total sales for the year.
  • Finally, you can find and select an already created name range (or cell for that matter) by selecting a name range from the Excel name box drop down.  You can also just simply type in the name range and it will automatically highlight the cells associated to the name range.

link

MORE ON THE EXCEL NAME BOX

Interested in learning more about the name box? David Ringstrom does an awesome job in his post for accountingWEB explaining 25 different ways you can use the Excel Name Box.

Twenty-Five Ways to Use Excel’s Name Box

David does a stellar job at reviewing pretty much anything and everything that the name box can do in Excel.   Once you start reading this I’m sure you’re going to want to send him a shout out for divulging all of that information to you.  He’s also great at getting back to people in the post so if you have questions , be sure to ask him in the comments.

Another great resource you can use is from Hasaan Fazal at Pak Accountants.  In this article he reviews different ways you can use what he calls the ‘nothing box’ – check it out:

15+ Uses of Excel Name Box – The ‘nothing’ box that has a ‘name’ is nothing like what you believe

Excel Tip # 3: Learn How to Use Pictures in Your Excel Bar Charts
STEP1There is nothing better than adding some fun and interesting features into your Excel spreadsheets.  One way of doing that is through using pictures in your bar charts. The first step required in doing this is the following:

  • Select the data series in your chart.
  • Right click and select format data series.
  • Select the fill option under format data series and change the type from solid to picture or texture fill.

STEP # 2In the second and final step, you’ll simply do the following:   

  • Select the File option under the picture or texture fill section.
  • Choose the picture / image you’d like to use from your computer.  Select it and click OK.
  • The image will be stretched out so the next thing you’ll want to do under where you selected the file is select stack. This will ensure that the picture is stacked as mini icons.

link

MORE ON USING IMAGES IN YOUR CHARTS

I’m pretty sure anybody that is serious about learning Excel has heard of Chandoo.  Honestly, there is no other website that contains more blog posts on the topic of Excel in my opinion.  He created a great little resource that you can refer to for additional information on using images in your charts.

How to create a column chart with background image in Excel ?

In this blog post he actually uses full blown images that span across multiple bars on his chart.  It’s really cool.  Here is one of the screen shots from his post:

image_background_chart_chandoo

Excel Tip # 4: Learn How to Use the Excel Camera Feature
STEP1 The camera feature in Excel is definitely one of my favorite.  It allows you to highlight an area on any spreadsheet/worksheet and then display a dynamic copy of that area that is easily resizable. Let’s look at how you can add the camera feature to your quick access toolbar:

  • Select the drop down arrow in the quick access toolbar and select more commands.
  • Once the Excel Options screen comes up, select all commands from the drop down.
  • Scroll down to the camera feature, and then add the camera to your toolbar, and hit OK.

STEP # 2Now that we have the camera tool setup in our quick access toolbar we can go ahead and select an area on our spreadsheet that we would like to copy and take a picture of.  Go ahead and select the area, and then click on the camera tool in your quick access toolbar.
STEP # 3In this last step, now that you’ve used the camera tool to copy an area on one of your spreadsheets, you simply need to left click to add/paste the image. The image is great because not only is it resizable and easy to move, but it is also dynamic in that if something changes within the range that the image was taken with the camera tool, the changes will automatically update on your picture/image.  This allows you to add multiple graphs, charts, sets of data and easily resize them.  This can be super handy when building dashboards with many sets of data, charts and information.
link

MORE ON PICTURES IN EXCEL CHARTS

Quite some time ago I created an image that covers all of the steps required in using the Excel camera tool in your dashboards.  If you’re interested in saving it to your pinterest account or if you just want a copy on your desktop feel free to save the image.  You can checkout the blog post here:

Excel Camera Tool: How to Use the Camera Tool To Build Excel Dashboard Worksheets

Excel Tip # 5: Learn How to Create Transparent Charts and Layer Shapes in Excel
STEP1 Getting your data ready for your chart is the first step in creating a transparent chart.  In this example, I want to display the average student percentage in a pie chart. To do this, you need to add an additional cell beside the average student percentage that subtracts 1 minus the percentage the students received. In my example, the formula will be (=1-B5).  This will allow you to then make a graph that shows you exactly how close your students were to 100 percent in a graphical format.  Notice that cell C5 does not display anything in the cell.  To do this, you can select the cell, and then go to format cells (CTRL +1), head down to the custom section and then type in 3 consecutive semi-colons (for an explanation on this, watch the video above).
STEP # 2The next step of the process is to create the chart.  Let’s walk through the numbered steps on the screen shot.

#1 – Select your data for the chart.

#2 – Head over to the insert tab and then select the pie chart icon.

#3 – Now select the first 2-D pie chart that is being displayed in the drop down.

#4 – The chart will now be created.  You’ll want to select and delete the title and the legend on the chart area.

#5 – Select the entire chart, right click and select format chart.  You’ll then head over to the fill section and select no fill and no outline.

STEP # 3The next step of the process just requires that you remove the fill from the calculated percentage, and add a white border.  Once you’ve done this, you’ll also want to add a white border on the data point entered by the user and then change the fill to the color of your choice.
STEP # 4The last and final step of the process will be putting together the visual display by layering shapes.

#1 – Create a shape from the insert tab and change the fill to a color that you feel is suitable.  Drag your chart over the newly created shape.

#2 – Hold the shift key down and then left click the shape and the chart.  Right click and then select group.

#3 – Add another shape and go to the formula bar and associate it to the cell where your user has input the percentage.  In this case I have a name range called avg.

#4 – Add an image in the top left hand corner of the display and then select the image and then the other shapes and chart and then group them once again.

link

MORE ON TRANSPARENT CHARTING AND SHAPE LAYERING IN EXCEL

Shape layering may be one of my favorite things to do in Excel. Placing functional objects over shapes like charts is even better.  I came across an amazing article on how to do this from Data Pig Technologies probably a couple of years ago now but it seems at the time of writing this article, their website and the blog post is having some issues.  That being said, you can checkout this post in the meantime:

3 Intelligent Ways to Use Shapes in Excel

Chandoo as always also has some pretty awesome techniques that you can apply to your dashboards by using the same type of shape layering techniques:

Use Shapes and Images to make Prettier Charts [Dashboard Tricks]

A Summary of the Covered Excel Tips

You can pretty much learn anything about Excel online these days.

That being said, these Excel tips should help you look like a genius around the office.  Let’s be honest, this list could be much longer but then I wouldn’t have had the chance to go into such detail for each Excel tip.

Because of that, I’m sure the readers would love to hear some of your favorite Excel tips in the comments below.  I know I would love to hear them.

Be sure to leave links back to your articles and once all is said and done, this post should be an even more complete resource for anyone who stumbles across it.