ExcelTip2Day-Shortcut, Trick and Solving Methods: September 2014

Monday, September 29, 2014

Compare Percentage Result Between Excel Formula [Average] And [Averageif Exclude "0"]

Do you know the result of percentage not the same if there is the number “0” within the Range of "Average" Formula?

Therefore, how do you solve this problem?

To obviously visible what the difference of using Average Formula between the range of the number “Include 0” and “Exclude 0”.

1] Let us have a look the first sample. We have the table of the whole year of the sales amount.

However, there are the two months, which are with the “0” sales amount(Empty cells), July and August.

We use Excel formula “=AVERAGE(AB5:AB16)” to calculate the percentage average from January-December. The outcome is 76.11%. That would be true?

2] The second sample, we use all the same data as the number one. On the other hand, we have changed the formula to use “=AVERAGEIF(AB5:AB16,"<>0")” instead. Here we gain 91.33%

It does not matter what our calculation criteria is, here, my article is just only to show you how the two formulas work in different way. You can pick any of them and use it as you want.

See more other tips:

Would you like to surf my other blogs?

Friday, September 26, 2014

How To Align Number To Be At The Center Of Active Cell(S) In Excel's Workbook.

Cell is with the decimal number inside it. The number usually keeps at the right of the border. Anyway, if you want to format those figures to be in the middle of cell's border. It is not too difficult at all to do that.

Hereafter is the step-by-step format cell to center the figures being in the middle:

Select the cells to be centered. (Cell "B5" ex. image below).
Click on the Format Cells > Number > Custom > To type #,##0.00 into the code box. (Type the among of digit as same as the data entered)

Before

Process

After

If it doesn't work. Align the format cell by clicking the center like below image.

See more other tips:

Would you like to surf my other blogs?

Thursday, September 25, 2014

How To Escape Away From Header And Footer To Normal View | Exceltip2day

With my real experience about using “Header and Footer”, function. I have been frustrated looking around for how to escape away from the view’s status of “Header and Footer”.

Firstly, I thought it should have the way out by clicking on the bottom of itself, “Header and Footer”.

After I had searched it for a while. I could make it out and want to share you all as bellow:

Method of away out from “Header and Footer” view to Normal view.

1] Hover your mouse to the “View” tab and click on it.
2] Click on any cell that out of the “Header and Footer” blank space.
3] Click on the “Normal” tab.
4] Immediately, save your file.

Header and Footer Status

Select cell A2 which is out of Header blank space

Become to Normal View

Then, try to close and re-open your file again. It should show you with the normal view. If not, repeat the method above again. (Do not forget to save your file immediately after your page become to “Normal View” status.)

See more other tips:

Would you like to surf my other blogs?

Sunday, September 21, 2014

Technical Tip To Select Or Copy Data Entered Between Range Of The Particular Cells And Uppermost Cell (A1)

One technical tip of Excel using of today that I want to share you all, it is the only private trick that I have tried many times to discover the simple way to copy the data entered between the range of particular cells and an uppermost cell (A1).

Eventually, I found the ideal way to copy or select the desired group of cells as follow:

As per the screenshot for an example to show, you right here. Suppose to select and/or copy the wanted cells, which begins from the cell “E13” and the cell “A1”. Including all cells in the range to the upward, cell “E1”, and to the leftward, cell “A13”

1] Hover your mouse to cell "E13" and "Click" on it. (In yellow)

2] Press "Ctrl" "Shift" "Home" buttons as the same time together.

Get DONE! Now, you can do whatever as you desire such copy, delete, format,...

See more other tips:

Would you like to surf my other blogs?

Saturday, September 20, 2014

How To Display The Data Entry In All Empty Cells On The Left Of Pivot Table

Why does Pivot Table not show me all data in the left column?

Hello friends, think you are one of those among of Excel users. Encounter the hidden data of Pivot Table.

Pivot Table function is one that be used very often. The outcome of Pivot Table report usually display with a brunch of empty cells on the left within its range.

Here we go on how to select the layout and show out the hind data in empty cells.

1] Once the Pivot Table is done, It usually looks like the printing screenshot below. Please, look at the yellow in column A, from cell "A5" downward. See that they are all empty.

2] Go to the "Design" tab in the Ribbon and click it > after that click on the "Report Layout" > then again click on the "Repeat All Item Labels"

3] Done! Notice that the words "Small Truck" have been displaying out from their hiding.

See more other tips:

Would you like to surf my other blogs?

Wednesday, September 17, 2014

Quick And Easy Way To Paste Data To Multiple Cells In An Excel Worksheet | Exceltip2day Blog

Do you know how to paste data to multiple cells in your Excel worksheet?

Therefore, whether you know or you don't; it does not matter at all. Because, I am going to tell you the trick to paste the data to cells as much as you want in an Excel worksheet with an easy way.

Look at and follow the method below:

Select the data in cell and copy it.
Use mouse to select any cells being pasted.
Press “Ctrl” and “V” buttons. (The data will be pasted immediately.).

See more other tips:

Would you like to surf my other blogs?

Saturday, September 6, 2014

[Resolve] Excel – Why Can't I See The "0" In My Excel Workbook?

If you are looking for what to fix the "0"[Zero Number] disappear when put at the beginning of the string of data entry.

Here I am going to share you the 2 ways of solving such that kind of.

1] Simply put an apostrophe [ ' ] in front of the number like this '000666. This mode might show you an error message but won't make any wrong to the numbers.

2] To set the specific format at the "Custom" category.

This step below will show you how to make it done:

Right click mouse, which is already onto the cell to be worked.
Click on the tab "Format Cell..."
On the "Number Tab" click the "Custom" that is usually at the lowest line from the Category list.
To fill out the "0..." equal to the digit number as required in the Type: space box. Ex. type 000000 (6 digits) as for 000656.
Click "OK" bottom at the below right corner of the "Format Cells Box"

See more another tips:

Would you like to surf my other blogs?