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

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

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

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

SOLVE YOUR BUSINESS PROBLEMS USING EXCEL.

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

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

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

 

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


 

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

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

 

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

 

Vlookup - Merge Data

 

 

DOWNLOAD THE WORKBOOK FOR FREE.

 

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

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

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

Vlookup Multiple Spreadsheets

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

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

Vlookup formula Screenshot

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

STEP 3: Calculating Months On Hand Using Other Formulas

Calculating Months On Hand

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

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

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

 

FREE RESOURCES TO TAKE YOUR VLOOKUP GAME TO THE NEXT LEVEL

VLOOKUP EXAMPLE EXPLAINED AT STARBUCKS

Excel Campus Starbucks Vlookup

THE VLOOKUP FUNCTION - AN OVERVIEW

The Vlookup Function

EXCEL VLOOKUP TUTORIAL FOR BEGINNERS

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

THE VLOOKUP BOOK

the Vlookup Book

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

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

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

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

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

TIP#1

 

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

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


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


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


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


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


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


TIP#2

 

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

Step # 1: Create the Shape


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


Step # 2: Edit the Text


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


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


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


TIP#3

 

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

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


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


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


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

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

 

SUM & IF Formula

 

The Formula Explained:

 

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

 =SUM(

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

 

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


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


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


The Budget & Expense Tracker Review

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

 

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

Excel Dynamic Conditional Formatting: Create a User Controlled KPI Dashboard

Excel Dynamic Conditional Formatting: Create a User Controlled KPI Dashboard

One thing that I love to save is my time.  Wouldn’t things just be that much better if you had a little more time?  I want to show you a quick and fairly painless method of allowing your spreadsheet/dashboard user to update target numbers to monitor salesperson performance.  What is going to save you time is showing you how you can use conditional formatting in Excel and combine it with an input range to build a dynamic key performance indicator table.  Instead of having to tediously go in and update target numbers every month from the manage rules options of Excel conditional formatting, you can painlessly update your targets by simply updating your input range.  Sounds easy, right? It is.  So let’s have a look.

(more…)

The Simplest Ways to Make The Best of a Sparkline in Excel

The Simplest Ways to Make The Best of a Sparkline in Excel

How do you make the best of a sparkline in Excel?  Well I’ve decided to answer that question in this post.  Excel sparklines are ideal for quickly plotting trends especially when you are looking at a data set / series or a report.  There are several features that you may or may not already know about, but I will review the top 5 things that I feel will help you make the best use out of Excel sparklines.

 

(more…)

Creating Pivot Tables 101: A How-To InfoGraphic

Creating Pivot Tables 101: A How-To InfoGraphic

You know the saying, “a picture is worth a 1000 words”? Well, I’m hoping that this post can convey to you in a quick, easy and visual fashion how to create a pivot table.  Please do not be shy and share it with everybody you know – or just post it on your social media accounts and maybe even your office cork board (do they still exist?). (more…)