⌨️ 𝔼𝕩𝕔𝕖𝕝 𝕋𝕚𝕡𝕤 𝕋𝕣𝕚𝕔𝕜𝕤 𝕒𝕟𝕕 𝕊𝕙𝕠𝕣𝕥𝕔𝕦𝕥𝕤

Hello there. I'm Michael Leng. Today, in my blog "Exceltip2day" , I'm going to talk about: How to use the “Select Object” tool in Excel? Tip Quiz What is the shortcut key to select the entire column? The “Select Object” tool is a very good function to handling all about the object like pictures, shapes, SmartArt… In this article, I am going to tell you how to use the “Select Object” for SELECTING multi-objects and delete them all. Do the following: On the ribbon bar, click the “Find & Select” Click over the “Select Object” tool. Press and hold down the SHIFT button. Drag mouse to select all over the objects you want. Press the “Delete” button. To be more understood, you can see all the methods step by step through all the images respective below. Figure no.1 Click the "Select Object." Figure no.2 Drag over the objects. Figure no.3 All objects are  selected. Figure no.4 Empty cell after de

Hello there. I'm Michael Leng. Today, in my blog "Exceltip2day" , I'm going to talk about: How to re-format the multiple data entered in an Excel sheet. Tip Quiz Do you know what is the Excel shortcut key for call out "Insert Function" Dialog Box? Do you know the very shortest way to format the entire cells in a worksheet? Fortunately, this following method is the best trick as far as I have ever known. It helps you to format the entire cells in a workbook to be the only one unique format. More, it does not complicate at all. Here comes the step-by-step shortcut: Select the original format cell as wanted. (Ex. Cell  “ B3 ” ). Click on the “Format Painter” icon on the top left corner. Press the “Ctrl” “A” buttons on your keyboard. Done! The outcome should be the same as the images beneath. Figure no.1 Shows the format of the entered data with none arrangement. Figure no.2 Shows the format of the entered data after

Hello there. I'm Michael Leng. Today, in my blog "Exceltip2day" , I'm going to talk about: How to use “Format Painter” Tip Quiz Is there any trick to exscape away Header and Footer? How can I use “Format Painter” for unlimited multiple pastes? As you might know that using the “Format Painter” Excel function is very useful and save time.  Nevertheless, not many people know the trick and enjoy using it in a very simple way. Trick is: 1] Select the specific cell(S) that you want to copy the format. (Ex. as the image below is cell "A2") 2] To click twice quickly without moving the mouse (Double-clicking) onto the “Format Painter” function button. 3] Click to any cell(s) you want to paste the format. 4] Click on the “Format Painter” again to inactivate the task regards no more use. Before "Format Printer" Processing "Format Printer" After "Format Printer" Tip Answ

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 &quo

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.  The for

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

Not only graph in the ribbon for ready use but also in the formula too. This method is very simple to use and look unconventionally. Thus, I am going to tell you how to create the mentioned graph with formula as below. 1. Make your own table in your Excel worksheet as same as mine. (See the image above) 2. Change font in cells between “D3” and “D8” to “Webdings” style. 3. Input formula =REPT(“M”,C3) into cell “D3” 4. Copy, paste and apply the formula as of no.3 above for the rest of cells between “D4” and “D8”. They will be liked these below after completion the formula. "D3" =REPT(“M”,C3) "D4" =REPT(“M”,C4) "D5" =REPT(“M”,C5) "D6" =REPT(“M”,C6) "D7" =REPT(“M”,C7) "D8" =REPT(“M”,C8) Alright, then it should appear the graph image with men symbols as image shown above. See more another tips: Trick How To Delete The Same Data In Multiple Places With An Easy Way Ctrl+Shift+_ Excel Shortcut Erase The Selected

In Excel worksheet, supposing, if there are some data that they are the same and filled in the multiple active cells. Then you want to delete all those same data from your worksheet. Yes, you can figure it out with a cool way as I am going to show as below method. Trick to delete the same data in multiple places: Press “Ctrl” “F” to display out the “Find & Replace” function box. Fill out the data to be deleted into the “Find” blank space (Here in the image below,"Before", is the word “Need” for example.) Click on the “Replace All” button. That’s all done. The word "Need" has disappeared as the second image,"After", beneath that is shown below.   BEFORE AFTER How did it work? The "Find & Replace" is used for finding the data to be deleted and immediately replaced by any data as wanted. In this article, I just leaved the "Replace" blank space empty,-none data being filed out. Therefore, the wor

Hello there. I'm Michael Leng. Today, in my blog "Exceltip2day" , I'm going to talk about: Ctrl + Shift + _ shortcut Excel. Tip Quiz What is the shortcut key to order printing? In Excel spreadsheet, if you want to erase lines border off. You can use a shortcut key to apply the task as method below: Method: • Select the active cell(s) where you want to line off the border (e.g. as the image below is B3 : D7) • Press the "Ctrl" button on your keyboard and keep it hold. • Press the "Shift" button and keep it hold too. • Press the "_" button. (underscore) Then release all the three buttons. Shortcut erase border lines in Excel sheet The square outline border will be off immediately. Note: This shortcut will help you to erase a square border with the only press the three shortcut keys. Shortcut for: Border lines off. Windows shortcut Ctrl Shift _ Did you find this article useful?

In Excel spreadsheet, if you have typed some extra spaces between words. Therefore, you can use TRIM(text) function in order to get rid of those unwanted spaces. This function will erase all extra spaces but except for single spaces between them. In addition the formula =TRIM(text) function will remove leading and trailing spaces from the text as well. Syntax TRIM(text) How to use formula: To type: 1] =TRIM into any cell. 2] ( 3] Select the cell wanted to be trimmed. (See the image below as example by putting cell A1) 4] ) It's done. Text: the text in any cell from which you want spaces deleted. See an image example below for understanding easily. Did you find this article useful? See more another tips: VLOOKUP Searches For Value From Table Array In The First Column Return Value The Same Row Formula “LOOK UP - Array Form” Automatically Looks Approximate Match Values First Row/Column Formula “LOOK UP” For Using Approximate Match Values Sorted Ascen

In Excel table with data entry, if you want to look for a value from a table array in the first column you refer to and get return an equivalent value in the same row from another column in the table array. This case, suppose you will search for the data from the table array from the right column and returns data associate in the left column and the same row. The formula to use is “VLOOKUP” Formula Syntax =VLOOKUP (lookup_value,table_array,col_index_num,range_lookup) * Data in the table array (F column) have to be sorted by ascending order, otherwise, the return value may give an unexpected/incorrect value data. Do you find this article useful? See more other tips: Formula “LOOK UP - Array Form” Automatically Looks Approximate Match Values First Row/Column Formula “LOOK UP” For Using Approximate Match Values Sorted Ascending Order In a List Method of Extract the Specified Characters from the End of Text String Content You might like my another blog: Learn Thai