Tuesday, May 27, 2014

Excel Trick Expand Excel Column Width Fit To The Longest Data Entered.

In Excel worksheet, the active cells with the data entered (numbers, letters, symbols, etc.) inside. Those scattered data entries, which are added and contained to cells, may have variety of length. The data in each cell that is narrower or wider than the typical columns width.

If you want to expand the column width (AutoFit Column Width), equal the longest data in the cells of all columns in the worksheet. I have a trick. Let us explore it together.

For example, see the image below:

Notice that in all the columns between A and D contain with some different data entered. However, we will not see all the text inside these cells. Because, the column widths in this worksheet are set to the standard length.

Therefore, if you want to see all the text, data entered, in each cell and every column as well. That means you must expand the width of all columns fit to the longest text that will cut off if the cells to the right contain data.

According to the above imply, hereafter the method to help you to deal with it.
  • Hover your mouse over the top left corner of the worksheet "Select all the cells" and click on it.
  • Then, hover your mouse to the area of the above vertical line of any column, to push the button on your mouse twice quickly (Double-clicking).

When the process is completed. You will have the column widths fit the length of the longest text as the example with picture shown the message that said "Can you extend some space for me fit the longest line in this column?”

Do you find this article useful?

Michael Leng is Thai, has a BA in Hotel management from Ramkhamhaeng University. He had worked for some hotels. Currently he works in logistics field, Bangkok, Thailand. Because of loving in English,  he has written some his own blogs in English-he really wants any feedback about his English he has posted.

See more another tips:
You might like my another blogs:

Saturday, May 24, 2014

[Ctrl] + [-](minus sign) Excel Shortcut Open Dialog Box Menu of Delete Cells, Row and Column Function

In Excel worksheets, it allows to you deal with the four functions of Delete in the DELETE dialog box as below:
  • Delete Shift Cells Left
  • Delete Shift Cells Up
  • Delete Entire Row
  • Delete Entire Column

Shortcut method to display the Delete Dialog box:
  • Press the “Ctrl” button and keep hold it.
  • Press the “-” (minus sign).
Then, release the two buttons as mentioned above lines.

Function Key
Result Description
[Ctrl] +[-]
To open dialog box menu of delete cells, row and column

See more another tips:
You might like my another blogs:

Wednesday, May 14, 2014

4 Cool Ways To “Sum” Values Of Number In An Excel Worksheet – ExcelTip2day

How do you sum values in an Excel worksheet? How many ways do you know to deal with it? Which way do prefer most?

Forget it if you are thinking about the answer. Today, I bring you the four ways of sum values in a worksheet that you can pick to use it as yours.

Look at images for your own illustrate practice.
i. To use Σ (Sigma sign)
  • Click on cell [A7]
  • Click on [Σ] symbol
ii. To use the two shortcut key
  • Click on cell [A7]
  • Click on [Alt] [=]

iii. Use formula “ =SUM() ”
  • Click on cell [A7]
  • To type [=] [sum] [(]; {equal sign (=), a formula (sum) and an open parenthesis ( ( )}
  • Use mouse click and select cells [A1] to [A6]
  • To type [ ) ]; {a close parenthesis}
iv. Use plus (+) and type on cell manually.
  • Click on cell [A7]
  • Type [=] [A1+A2+…A6]

Hope you would love one out of four ways to calculate yours number. To me, I like to use the second method. I think it is cool.

See more another tips:
You might like my another blogs:

Sunday, May 11, 2014

How To Use [AVERAGEIF] Formula "Not" Including “0” (Zero) In Range


To set up formula of AVERAGEIF into the cell to auto calculate the average sales amount for the whole year while update data in every month (Year To Date).

Syntax of AVERAGEIF formula function:

=AVERAGEIF (range, criteria, average_range)

  • Range: Required (Must fill out). One or more cells to average, including data entered such as arrays, numbers, names, or references that contain numbers.
  • Criteria: Required (Must fill out). The criteria in the form of a cell reference, number(s), expression, or text that defines which cells are averaged. For example, criteria can be expressed as 123, "123", "<>123", ">123", "Student", "BlogMaster" or A1.
  • Average_range: Optional (Non-compulsory). The actual set of cells to average. If it’s omitted, range is used.

In column, B2-E2 they have been being felt the sales amount as:
B2 = 200, C2 = 230, D2 = 252, E2 = 232

In column F2-M2 are empty.

In column "N2", to set up formula =AVERAGEIF (B2:M2, "<>0")
The average result between B2-E2 is equal to 228.5


Notice that even the range is between B2 to M2. Nevertheless, the "0"(Zero) number from E2 to M2 will be thrown off the formula calculation.

The output solution is calculating by average the amount between "B2-E2" only. Once, the sales amount of May, Jun,-Dec have been updating, the average sales amount will include too.

Michael Leng

See more tips:

    Saturday, May 10, 2014

    [Shift + Space Bar] Excel Tip How To Select The Entire Row of Excel Worksheet

    Do you know what to do if you need to select the whole cells in a row?

    There are two ways to operate as I am going to tell you now below:

    1] To click on the number of any row to be selected.

    For example, we want to select the row no. 5, therefore, hover your mouse over, which is you want to and click onto it such as the row no. 5 as shown you as the image below.
    If you would like selecting for multiple rows. You can move your mouse cursor to click on any number of rows at the farthest left hand as many as you want.

    For example, if you want to select the row no. "1" to no. "19", move mouse cursor and click to the row no. "1" and keep pressing while moving cursor down until the row no. "19" then, release your mouse. All the rows between no. "1" and "19" will be selected and indicated with dim highlight on them as the image below.
    2] To use shortcut key on your keyboard for selecting the entire cells in row(s) in Excel spreadsheet.

    Method as following:
    • Move mouse cursor to any cell in the row(s) that we are going to select them.
    • Then press the two key buttons; [Shift] & [Space Bar] on your keyboard.
      Okay, Done! You should see the entire cells in the row(s) with highlight shown as image above.

      Shift + Space Bar
      Selecting the entire row(s) from farthest left to the right side ending edge.

      See more another tips:
      You might like my another blogs:

      Friday, May 9, 2014

      3 Tricks (9 Steps) To Adjust Excel Worksheet Screen Size As 'Zoom In' And 'Zoom Out'

      Let’s see the 3 methods to do for “Zoom In” and “Zoom Out” Excel worksheet as below;

      1] To use "Ctrl" and "Scroll Wheel" button.

      Step by step for “Zoom In”

      • Press "Ctrl" button and keep it hold on.
      • Roll "Scroll Wheel" forward.

      Step by step for “Zoom Out”

      • Press "Ctrl" button and keep it hold on.
      • Roll "Scroll Wheel" backward.

      2] To use Mouse moving the "Zoom Slider" as method below;

      • Right click Mouse onto "Zoom Slider" and move to the RIGHT side for “Zoom In”
      • Right click Mouse onto "Zoom Slider" and move to the LEFT side for “Zoom Out”

      3] To use function "Zoom" in the “Ribbon Tab” as step by step below:

      • Click Tab "View" at the Ribbon
      • Click command "Zoom"
      • To adjust the percentage of “Zoom” as you want

      See more another tips:
      You might like my another blogs:

      Thursday, May 1, 2014

      [Monitor Screen Accidentally 'Upside Down' Problem] Solving Methods And Tricks | ExcelTip2Day

      Good morning. Today, I'm going to tell you about:

      "How to fix Monitor Upside Down"

      I am Michael Leng, my today's post is with the two parts. Firstly, I'm going to talk about the way of the Monitor Upside Down fixing. Then, I'll take you to the similar of screen display in the improper way.

      ↣Now, let's start with first section;

      Once encounter such the bad problem, the PC screen upside down, I got a mad. After I have solved it, I really want to share with you.

      Screen Upside Down

      I have experienced while working on an Excel spreadsheet, the screen of the monitor has upside down without intention. At first, I panicked and did not know how to tackle such that problem I have never encountered before.

      However, finally I could find the solution to handle it smoothly.

      Well, if you touch your keyboard accidentally and it activates your monitor screen upside down!

      Don’t panic. It’s very easy to solve that kind of unexpected occurrence. Find out and use the three letter keys on your keyboard of PC/notebooks and follow shortcut trick below:
      • How to solve it:

      Just press "Ctrl"+"ALT"+"Up Arrow" buttons all together at the same time and then release them.

      It will resume your screen in the correct direction as its original status.

      What makes it upside down?

      While you are trying to type your order on keyboard, you have hit the ‘Ctrl+ALT+Down Arrow’ without intention.

      ↣Here we go the other related screen problem solving shortcut key:

      Moreover, there are some similar trick can be applied to solve such the wrong screen display as below too;

      • Ctrl +Alt + Right Arrow – Result screen rotate the left side up (90 degree)
      • Ctrl +Alt + Left Arrow – Result screen rotate the right side up (270 degree)
      • Ctrl +Alt + Down Arrow – Result screen rotate the upside down (180 degree)

      Hope the tips how to solve the computer monitor upside down will help you to fix your problem accordingly.

      See you again.

      Michael Leng

      See more another tips: