The Ribbon is Excel’s command menu interface. It organizes commonly used actions together in an intuitive and visual way. These are the main parts of the Ribbon. What Tabs Are In The Ribbon? There are 7 Tabs in Excel’s default setup. In addition to these 7 Tabs, there is an 8th one called the Developer tab that is hidden by default. You can read this post to find out How To Enable The Developer Tab....
A few days ago, I was working with a data set that included a list of companies in Excel along with their logos. I wanted to place the logo of each company in the cell adjacent to its name and lock it in such a way that when I resize the cell, the logo should resize as well. I also wanted the logos to get filtered when I filter the name of the companies....
In this post we’re going to make a pizza pie… chart. Not as delicious as real pizza, but this is a cool trick to jazz up your charts and involves changing the fill option to a picture for your various data points. This trick works for other charts like bar charts and you don’t have to fill them with a pizza picture, you can use something more relevant to your data, so if you’re an office supply company and you wanted to make a graph showing your top 5 products you could use pictures of pens, pencils, staples, paperclips and paper....
Highlight a column in the data and open the Go To window. You can also use the Ctrl + G keyboard shortcut to access the Go To window. Now only the blank cells in the column you highlighted will be selected. Delete these rows. A similar trick will work for blank columns, just select Entire Column instead of Entire Row. Voila! You just saved yourself a tonne of tedious work....
The ability to merge cells is one such feature. While I don’t use this option myself, I still find myself unmerging cells (mostly in the workbooks shared by other people). In this tutorial, I will show you some ways to quickly unmerge cells in Excel. While I don’t think people are going to stop merging cells anytime soon, I hope these simple ways to unmerge cells will save you some time and frustration....
What’s in the data? Sales Representative – 5 different sales representatives including 3 in the East and 2 in the West.Location – State the sale occurred in. 8 Different States in the data.Region – Region the sale occurred in, either East or West. 4 States in the East and 4 States in the West.Customer – Customer’s name on the order. There are 530 different customers in this data.Order Date – Date the order was made by the customer....
Yep, Excel can do pretty much everything, and that now includes sending texts. But here’s the deal. This is going to cost a little bit of money unless you just want to send texts to yourself, in which case you can use a trial account. But it’s actually probably cheaper than most pay as you go phone plans for texting. The whole setup is going to use Microsoft’s Flow Excel add-in and Twilio....
You might need to count the number of words in your text data for many reasons. Perhaps you have comma-separated lists and need to count the number of items in each list. Unfortunately, Excel doesn’t have a built-in method for counting words. But there are a few clever ways to get the result you need. This post will show you how you can count how many words are in your text data in Excel....
Yes or No is a common replacement for the Boolean data type where the Yes value is interpreted as true and the No value is interpreted as false. This can make the information in your Excel dataset easier to read and interpret the meaning for your users. This post will show you all the ways to create a yes or no in Excel. Add Yes or No with a Dropdown List The best way to add a yes or no value to your workbook is by using a dropdown list....
Before I come to this month’s template, I have a favour to ask. If you have any ideas for Excel Templates, do share it with me (email me or leave a comment below). It will help me create stuff that is useful for you and my other readers. Also, if you are already using a template, and would like to share it with others (and get yourself featured on my blog), just email me or leave a comment below....
A proper understanding of how to copy-paste multiple cells (that are adjacent or non-adjacent) would really help you be a lot more efficient while working with Microsoft Excel. In this tutorial, I will show you different scenarios where you can copy and paste multiple cells in Excel. If you have been using Excel for some time now, I’m quite sure you would know some of these already, but there’s a good chance you’d end up learning something new....
This is a pretty popular way to display a fundraising campaign’s current raised amount versus their goal or target amount. Let’s look at how we can build this thermometer visual from scratch in Excel (and PowerPoint). Video Tutorial Creating A Thermometer Template In PowerPoint We’ll need to head over to PowerPoint to create our thermometer template. As I explained in my traffic light dashboard visual post, PowerPoint has some extra merge shape commands we need to use to combine and cut out our thermometer template....
The work was simple. There were many folders on the network drive and each folder had hundreds of files in it. I had to follow these three steps: Sounds simple right? It was – Simple and a huge waste of time. What took me three days could have been done in a few minutes if I knew the right techniques. In this tutorial, I will show you different ways to make this entire process super fast and super easy (with and without VBA)....
Ok, by “top secret“, I actually mean a database API made publicly available. And by “hack into“, I actually mean use Excel’s From Web Power Query functionality to access the API. Governments have a lot of data covering agriculture to renewable energy, and the cool thing is anyone can access it and they provide it all for free with an API. Data is always a valuable resource to have at hand when you’re trying to learn Excel too, so learning how to fish for your own data is definitely worthwhile....
While there is no built-in option in Excel to convert an entire Excel file to Word, you can easily copy and paste the data from an Excel document to Word. In this tutorial, I will show you multiple ways to copy data from an Excel Worksheet into a Word document. Copy Paste Excel Table as Plain Text in MS Word If all you want is to copy and paste a table from Excel to Word, you can do that with a simple copy paste....
Let’s say you have sales data for your company and you need to send each of the sales representatives in the company a copy of their sales. You might not want to share the entire set of data with each rep but just their own sales due to privacy concerns around their commission based compensation. To do this, you will need to take our original set of data and parse it out into many different workbooks (one for each sales rep) based on the sales rep column in the data....
Below is the written tutorial, in case you prefer reading over watching the video. In Excel, there are two kinds of tick marks (✓) that you can insert – a check mark and a checkbox. And no… these are not the same. Let me explain. Check Mark Vs Check Box While a check mark and a checkbox may look somewhat similar, these two are very different in the way it can be inserted and used in Excel....
If you want to view your performance versus a target for some metric like sales then a thermometer style chart is a great way to do this. In this example we have a table with columns for the sales person, their target sales and the actual sales they had. We will create a 2D bar chart for this data. Add your performance data to the secondary axis (in this case the sales data)....
It’s a common scenario to try and explain the change in financial results from year to year or quarter to quarter. But if your financials are in a Power BI dashboard, then there is no way to add commentary to explain any change in results. We can write data back to our source with PowerApps though! In this post, we’re going to take a look at how we can integrate Power BI and PowerApps....
It can be found in the Data tab in the Get & Transform Data section of the ribbon. It’s very powerful and also very easy to use and the query editor has a super intuitive user interface for an Excel user. Many transformation steps can be easily performed from the power query editor ribbon and you don’t need to know any code to clean and shape your data. Behind the scenes of the user friendly editor, Excel is translating each step in your transformation process from those ribbon commands into the power query M code language....
