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

by Brad Edgar | Feb 1, 2016 | EXCEL BEGINNER, EXCEL TIPS

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.

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.

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:

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.

#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).

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)

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.

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:

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

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.

#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.

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

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

by Brad Edgar | Jan 23, 2016 | EXCEL INTERMEDIATE, EXCEL TIPS

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!

[et_bloom_inline optin_id=”optin_7″]

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

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!

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.

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

Although 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.

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

There 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.

In 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.

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:

Excel Tip # 4: Learn How to Use the Excel Camera Feature

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.

Now 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.

In 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.

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

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).

The 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.

The 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.

The 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.

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.

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

by Brad Edgar | Dec 26, 2015 | EXCEL INTERMEDIATE, LEARN EXCEL FORMULAS

SOLVE YOUR BUSINESS PROBLEMS USING EXCEL.

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

SIGN ME UP

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

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

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

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

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

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

DOWNLOAD THE WORKBOOK FOR FREE.

DOWNLOAD

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

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

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

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

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

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

STEP 3: Calculating Months On Hand Using Other Formulas

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

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

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

FREE RESOURCES TO TAKE YOUR VLOOKUP GAME TO THE NEXT LEVEL

VLOOKUP EXAMPLE EXPLAINED AT STARBUCKS

CHECK IT OUT

THE VLOOKUP FUNCTION - AN OVERVIEW

CHECK IT OUT

EXCEL VLOOKUP TUTORIAL FOR BEGINNERS

CHECK IT OUT

SUGGESTED PRODUCT FOR HELPING YOU LEARN THE VLOOKUP FORMULA

THE VLOOKUP BOOK

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

CHECK IT OUT

The Excel Sales Trend Dashboard

by Brad Edgar | Nov 6, 2015 | LEARN EXCEL DASHBOARDS

THIS WEEK’S EXCEL TIPS

TIP # 1: How to Create Mini Chart Overlays on Your Dashboards

TIP #2: How to Add Data Callouts to Your Excel Dashboards Charts for High/Low Values

TIP #3: How to Add Excel Data Validation to Your Dashboards

THE FEATURED PRODUCT

For decades, managers and executives around the world have been monitoring sales trends to capture and understand sales cycles in their industries. In this Excel Sales Trend Dashboard, I’ve created a dashboard that automatically pulls your raw data from a table and displays the monthly sales trends for the data that you’ve added. Checkout the video to see this dashboard in action.

TIP # 1: How to Create Mini Pie Chart Overlays on Your Dashboards

Adding mini chart overlays to your dashboard allows you to associate the numbers in your dashboard to a visual display. Here is a birds eye view of the steps required to do this. For more details be sure to checkout the video above.

STEP #1: In this dashboard, you want to display the total percentage of sales for the top or bottom customer against the total sales for the month selected by the user. To do this, automate your data calculations based on what month is selected by the user. Calculate the highest sales and lowest sales by using the max and min values respectively and then by dividing each by the total sales for the month selected. Once you’ve got that calculation, in another cell subtract the percentage calculated by 1 and this will give you the remaining percentage for each the highest/lowest sales.

STEP #2: Now to create the graph, select the cell with the percentage of total sales for the highest value sold and select the cell that contains the value of 1 – that percentage. Go to insert and then insert a pie chart graph.

STEP # 3: Select the chart background and remove the outline and background by selecting no fill or no color. Next you can alter and adjust the pie chart. The value remaining percentage should be set to also have a no fill background but you’ll include an outline. For the percentage of total sales displayed, you’ll choose a background fill and make the outline the same color as the other percentage.

TIP # 2: How to Add Data Callouts to Your Excel Dashboard Charts for High/Low Values

Adding data callouts to your dashboard chart to show the highest and lowest values is a great way to quickly point out in what months of the year you’ve performed well or poorly. Let’s look at the step by step process for doing this.

STEP #1: You’ll need 2 additional columns or series that will be included on your dashboard chart. These will be added to your calculations tab of the worksheet.

STEP #2: Use the IF formula to display #N/A (using the NA() formula) if the associated value for the sales for the given month is not the highest (under the highest column data set). The IF formula will use the rank formula to right of your data set and will use the highest rank value to find this value (see video). Apply this same logic to the lowest value sold column. Note that the NA() formula is what is going to ignore those data points on your chart instead of putting a value of zero in.

STEP # 3: Add the lowest and highest value sold data series’ to your dashboard chart. We will use a line chart again for the type but we will only display the point. Select the data points and go to add data callout. You can the adjust and change the mark fill and the callout fill and color to be green and red depending on what value you are display (i.e. lowest or highest).

TIP # 3: How to Add and Use Excel Data Validation in Your Dashboards

Using data validation in your dashboards is a great way to identify whether a certain condition is met or not. In this dashboard, I want to know whether or not my raw data set is including more than 12 months of data. If it is, I want to display a red X and if it isn’t, I want to display a green check mark. Here’s how to do it.

STEP #1: In a cell on your calculations tab, go to insert and then symbol. Find the check mark and the X under the wingdings 2 font. Once you’ve added them, you can the change the font in the cell to get the standard font characters that will need to be used on the front end dashboard.

STEP #2: Use the IF formula to determine if our list of months name range contains more than 12 months of data. If the count is greater than 12 then display Yes, if not display No.

STEP # 3: Create a separate IF formula that uses the no or yes value in the last cell to display the wingdings X character if the value is Yes and the check mark if the value is No.

STEP # 4: Pull the formatting over to your dashboard cells where you want this displayed using name ranges for the cells on the calculations tab. Now you can change the color of the Wingding 2 font display by using conditional formatting on the cell. If the value in the cell refers to a check mark, display the value in green and if it’s an X, display the character in red.

The Excel Sales Trend Dashboard is a great way to capture your company performance in a minimalist way. It helps you understand where you’ve performed well and poorly and at the same time allows you to identify your best and worst customers quickly and efficiently. Downloading the dashboard is a great way to help you also learn some of the techniques used in the dashboard to get yourself acquainted with some useful Excel techniques. Whether you’re looking to learn or looking to cut time off your current processes, this dashboard is for you.

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

by Brad Edgar | Sep 25, 2015 | EXCEL INTERMEDIATE, LEARN EXCEL DASHBOARDS, LEARN EXCEL FORMULAS

THIS WEEK’S EXCEL TIPS

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

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

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

THE FEATURED PRODUCT

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

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

TIP#1

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

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

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

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

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

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

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

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

Step # 1: Create the Shape

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

Step # 2: Edit the Text

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

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

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

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

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

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

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

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

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

The Formula Explained:

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

=SUM(

IF will be used to SUM only the values in our value of purchase column (on the expense entries table) where the associated expense type in our Budget & Expense Tracker equals the same expense type (Select Purchase Type Column) on our detailed expense entries table/sheet. So if B5 equals the purchase type on our expense entries table, our true statement in the IF formula will add all of our entries when true or will add 0 for the lines that are false.

Once you’ve entered your formula and closed the last bracket, type CTRL + SHIFT + ENTER. This will make your formula an array. The reason why you want to do this is because you want the formula to not only find the first record where the expense type equals the select purchase type on your expense entry table but you want to sum an array of numbers (multiple entries) where this statement/formula is true.

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

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

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

The Budget & Expense Tracker Review

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

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

« Older Entries

Next Entries »