Excel Tips, Tricks, and Shortcuts

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 between using Average Formula between the range of the number “Include 0” and “Exclude 0”. 1] Let us have a look at the first sample. We have the table for 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 “=AVERAGE IF (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 a d...

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: How To Escape Away From “Header And Footer” To "Normal View" | Exceltip2day Technical Tip To Select Or Copy Data Entered Between Range Of The Particular Cells And Uppermost Cell (A1) How To Display The Data Entry In All Empty Cells On The Left Of Pivot Table Quick And Easy Way To Paste Data...

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: Techn...

Hello there. I'm Michael Leng. Today, in my blog "Exceltip2day" , I'm going to talk about: How to select the cells range  Tip Quiz What is the shortcut to minimize an Excel workbook? 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 at the same time to...

Hello there. I'm Michael Leng. Today, in my blog "Exceltip2day" , I'm going to talk about: How to use the " Repeat All Item Labels"  in PivotTable Tip Quiz What is the formula to calculate any date in Exel? Why does Pivot Table not show me all the data in the left column? Hello friends, think you are one of those among Excel users. Encounter the hidden data of the Pivot Table. Pivot Table function is one that is used very often. The outcome of the Pivot Table report usually displays with a bunch 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 ...

Hello there. I'm Michael Leng. Today, in my blog "Exceltip2day" , I'm going to talk about: The trick to paste the data in the multiple cells in a single paste. Tip Quiz What is the formula to move the unwanted spaces in the cell(s)? 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 into 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 the cell and copy it. Use the mouse to select any cells being pasted. Press “Ctrl” and “V” buttons. (The data will be pasted immediately.). Figure no.1 Show the cell to be copied. Figure no.2 Show how to select all the cells to be pasted. Figure no. 3 Show all the cells be pasted with the data entered. Tip Answer  a.  The formula is =trim().  b.  ...

You will find how make the "0" [Zero Number] to be seen in your Excel's cell(s) in our today's tip 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 with you the 2 ways of solving such that kind of problem. 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...