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!
GET EXCLUSIVE ACCESS TO CONTENT UPGRADES NOW & ALWAYS
Signup and get access to free content upgrades now and always. I'll send you your password to access the exclusive content once you confirm your subscription.
You have Successfully Subscribed!
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.
- 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!
MORE ON THE EXCEL FILL TOOL AND OTHER EXCEL TIPS
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
- 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
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:
- 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.
- 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.
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:
- 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.
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:
#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.
#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:
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:
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.