We Naviarone, have been helping people to explore excel and to complete their tasks quicker. You can manipulate your data in such a way that you might never have thought or heard of such things could be done in excel. In this article, let us learn how to calculate average, number formatting, handling the blank cells, and about a new graph – the combo graph. We are sure that this would help you in a great way to present your excel sheets with apt formatting.
We generally format numbers by separating the digits with commas and with two digits precision. Apart from these, we can also extend our formatting based on the type of number we manipulate. For example, it is possible to represent the dates in different formats like MM/DD/YY, DD/MM/YYYY, etc. To start with, let us look at how to do the general number formatting.
Highlight the cells and press these keys at the same time and you are done!
Shift + Ctrl + 1 – quick number formatting
Look at the un-formatted numbers
And after formatting, the result is as follows.
You can even Bold the values by pressing Ctrl + B.
Border the cells
Once we are done with our manipulations, the bordering of cells appropriately can highlight the actual data. Borders enable everyone to understand what is there easily. Hence it is a better practice to present your results or output cells with single or double borders. These two are not the only choices; you can border your cells as you wish by following these steps.
Step-1: Highlight the cells for which you need a border.
Step-2: Go to the Home tab from the menu bar.
Step-3: Click on Borders and then select the border type.
Let us select the ‘All Border’ and just look at it and you would get the cells bordered on all sides as below.
How to find the average for values in your excel sheet?
MS excel has so many functions that help you perform mathematical calculations quickly. If you have the revenue generated by different representatives of your company, then you can easily do various calculations using these functions. One among those is the Average function and as the name says it finds the average value for a set of data. In this case, you can identify the average revenue generated by each representative and it is helpful to decide on who performs well. This is a great chance to reward them accordingly so that they get motivated and works towards the organization’s growth.
=average(highlight the data range)
- Start typing the function with the ‘=’ symbol at the beginning. You will see the list of functions matching with what you have typed. Select Average from it.
- Highlight the data range and close the parenthesis and press enter.
- You can see the result instantly.
Tip: To find the average for more than one cell, you can either copy paste the cell to another where the formula gets copied and the corresponding result appears there. Or else, you can highlight all those cells for those you need the average and then type the average function. When you are about to press enter, just use Ctrl + enter. Now you can see the average has been calculated for all the highlighted cells.
Handling Blank cells
We encounter lots of blanks values when we actually come across the real-time values. Consider that we have a set of representatives and their sales for a particular week. What happens if an employee takes off on a day or two? Then the sales values for that representative for those days would be blank. When we represent sales, blanks obviously means zero. So we are in a situation to change those blanks to zeros now. If we start changing those manually, it is a mere waste of time! But excel has two different options to do it quickly.
Change Blanks to zeros: Option-1:
- Highlight the data range.
- Just press F5 and you would see a pop-up window named ‘Go To’.
- Click on the Special
- Choose the option, Blanks
- Now the cells with blanks on your sheet will get highlighted.
- Type ‘0’ on any one cell and press Ctrl + enter.
- All the blank cells with be getting the value as ‘0’ in just a shot!
Change Blanks to zeros: Option-2:
- Highlight the data range.
- Press Ctrl + F and you would see the pop-up window named ‘Find and Replace’.
- Leave as it is in the space for Find what and type a zero in the space for Replace with.
- Just click on the ‘Replace All’ button. The job is done! All the blank cells would be replaced with zeros.
- You will also get a pop-up window showing the count of the replaced cells.
Custom Combo Graphs
The numbers are really difficult to be understood and if the same is represented graphically, it gives a clear a picture of how our data actually go! Rather than confusing others by showing the revenue as numbers, it is a good idea that you can go ahead by representing with graphs. Combo graphs are newer ones and you can customize it per the need. Let us look at how to insert the combo graph to our sheet.
- Highlight the data range.
- Go to the Insert tab of the Menu bar.
- Click on the Combo chart and you would see the choices under it.
- Select your custom chart and you are done with the graph!
It looks easy, right? Of course, it is! Even the styles & colors of the graph can be changed and therefore do try these charts once. The next time you would use it by default. Such a great feature it is! Here is the chart that we have inserted into our data.
We are done with the topics for now and do leave us your comments or queries, to help you better. Keep watching videos and reading articles from Naviarone!