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.
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. 
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
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:
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
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.
Hey Brad,
Thanks for the mention. Really honoured to secure a line on your amazing blog with other Excel legends 🙂
You have great content my friend! Thanks for dropping by to check it out.
Tip#4 was new to me, and very useful. Thanks for sharing
That’s always great feedback to get. I’m always happy when someone picks up so new techniques for their Excel tool belt.
Thanks Johan!
Great article Brad! Lots of useful tips and your videos are awesome!
I also use the name box to navigate to Excel Tables throughout the workbook. It’s a great way to jump to a Table that is on another sheet.
Have a good one! 🙂
Thanks Jon!! Absolutely. I feel like the name box is under-utilized for sure. Thanks for checking things out Jon!
Nicely written. Well explained
Thanks Rajeev. I appreciate it. Also, thanks for checking it out!
Excellent tips, thanks a lot.
Here are 25(!) ways to use the Name Box in Excel.
http://www.accountingweb.com/technology/excel/twenty-five-ways-to-use-excels-name-box
Best Regards,
Meni Porat
Hi Meni – thanks for checking it out! I actually captured that article under the name box section of the article but it never hurts to have it again in case someone missed it. David did a great job capturing pretty much every name box technique you could think of.
Thanks again
WOW – never new Excel had the camera function – that was awesome!
Yeah it’s definitely an awesome feature. Thanks for checking it out Janice!
I really enjoyed the article, and hopefully I will receive new tips through email. Thank you.
Hi Abir,
I’m glad to hear you enjoyed it. Yes definitely! If you’ve signed up for the newsletter watch out for new content.
Cheers
Brad
Many Excel features are unnoticed only by way of this tips, we learn more. Thanks for the tips, keep going on.
Thanks Sridar. I appreciate your kind words and I am glad they were useful. Feel free to join the newsletter for additional tips too.
Hello there, I am an advanced user of Excel but still I would accept, picked up some new cool things from these tips.
Zahid – I’m very happy that you found a few useful tips in here. Thanks for checking it out!
Thank you Brad. Great tips! Appreciate you sharing.
No problem at all Anthony! I’m glad you enjoyed them.
Great blog with very useful (and new to me) tools to use :). Thank you Brad, I look forward to new tips!
Thank you Joeie I appreciate it.
There will definitely be more to come. Stay tuned!
Wow never knew excel has such amazing features.
There’s always something new you can learn about Excel for sure.
Tip #1 is something I use every time I install Excel. I have a workbook with three custom lists: The Greek alphabet (alpha, beta, gamma,…), the Latin alphabet (a, b, c,…), and the Military alphabet (alfa, bravo, charley,…). Adding these makes it easy to create sample data quickly, on the fly.
Yesss!! That’s awesome Jon. It was definitely quite the discovery for me. Great for business/industry specific data sets (e.g. Machine numbers in a mfg plant). Thanks for checking it out and appreciate you adding notes on how you use this feature.
I was at a company doing a training session, and I made up a dummy data set in 20 seconds. Adding the Greek letters was so fast that only a few people noticed. But one guy called out, “Is that a custom list? Awesome!”
One of my Google Drive folders is :Add-Ins and Setups”, which contains all the files I need on a new computer. It includes all the essential addins (MZ Tools, Code Cleaner, Smart Indenter, Name Manager, my own add-ins), a few registry hacks, my custom template files, some license keys, and my worksheet of custom lists.
Lots of great examples there for sure Jon!
I personally think the registry hacks would be really slick
Maybe I need to write an article, The Essentials of Installing Office.
I think it would be a hit!