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

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

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.