I think many people know how to AutoFit column width in Excel. In case you don’t, you can simply move your cursor in between the column headers, when you see a cross with left-right arrow, double-click. Then the column width will be adjust to fit the longest text string underneath the column.
However, AutoFit may not result in a nice-looking table as it gives you columns with inconsistent column width like the one below:
Continue reading
As mentioned in my previous post, I am going to talk about another built-in function that is not commonly used (in my limited community)… 😛
COPY, PASTE linked picture (aka “Camera”, which is actually available in Excel 2003; not sure if even earlier)
I think the term “Linked Picture” is more descriptive. Excel 2010 tries to make it more visible by putting it under Paste Special options but I doubt how many users have noticed that…
Continue reading
“Copy and Paste Value” is probably one of the most popular actions taken by many people every day. A probably fastest way to do so is definitely worth sharing.
I learn this from a comment to the post (http://www.myonlinetraininghub.com/favourite-excel-shortcuts) by MYNDA TREACY
It inspires me to explore more about CTRL C, CTRL V, CTRL, Different shortcuts… And here come this post.
Continue reading
在 Excel 中舜間轉移
Teleportation is still an imagination in reality; it is not in Excel world.
When you are working with a large workbook book with many worksheets, or even many workbooks with many worksheets altogether, you are likely to go back-and-forth to different ranges in different worksheets (be it same workbook or not). Sometimes, I am lost in worksheets and wondering if there is a Teleportor that can take me to wherever I want instantly…
Fasten your seatbelt! Here we go!!!
Continue reading
UPDATE on 7/24/20202: Don’t miss the video at the end of this post.
Did you ever ask why Excel does not provide Dropdown Calendar for date selection? Like the one shown below:
I did. I asked that because I didn’t know its existence in Excel as it is so well hidden. So I would like to share with you, just in case you need it. Continue reading
From a simple textbox, a rectangle to a chart, you may have experience in moving/resizing/aligning or even selecting multiple objects in excel. It could be really time consuming and the result may not be good looking still. :O
Have you ever thought about how nice would it be if you could select objects in Excel like the way you do in PowerPoint???? Indeed that is possible.
Continue reading
如何在EXCEL轉換量度單位?
Have you ever wanted to convert Degree Celsius to Degree Fahrenheit or Meter to Mile in Excel?
Yes, I did. What I did was to first Google the formula for such conversion, and then apply the formula manually in Excel, e.g. to change 37C to 98.6F, I would input a formula =37*9/5+32
One day, I realized that there is such a convenient function to do it…
Continue reading
How to fill up all the blank cells in a Table in less than 1 minute???
The table on the left is a typical output from Pivot Table. The blank cells help to make the table more “reading-friendly”. However they are not “analysis-friendly” as you cannot apply auto-filter correctly with that layout, not even think about to use formula like SUMIF, etc. (Note: In Excel 2007 or above, you can ask the Pivot Table to fill up the blank cells for you).
To convert [hour:minute] into number of hours quickly.
If you are working in HR field, you may need to calculate wages for part-time staff whose salary is paid on hourly basis. So you need to convert 8:45 (8 hrs 45 mins) into 8.75 hours, how to do it in Excel?
The solution is as simple as Multiplying TIME (time format) by 24, i.e. [TIME] x 24 = Number of hours (remember to format the result as number with decimals)
It makes total sense if you know how Excel stores time as value.
Continue reading
There are many ways to find out the cells in column B which are different from the cells in column A, e.g. using Conditional Formatting, Using formula in column C. These methods require some knowledge in Formula. Nevertheless, It could be a headache to many people with “formula-phobia”… ;p
Look at the screenshot below. The task is to highlight the cells in column B that are different from that in column A. OMG, is it an eye-test?
No worry. A few clicks can accomplish it.
Continue reading
This is an extension of the previous post that talks about Go to Special -> Visible cells only
Apart from Copy ONLY unhidden rows/columns, another situation we used to run into is probably deleting or modifying hidden cells unintentionally. Sound familiar to you?
Continue reading
Have you run into a situation that you wanted to copy a table, like the one below, to a new worksheet.
However the Table “expanded” automatically…
All the information from the hidden rows and columns reappears. What???
A simple trick can avoid this – GO TO SPECIAL: visible cells only
Continue reading
Excel tips: F4 to repeat LAST action
As mentioned in my previous post, F4 is my favorite keyboard buddy. It helps you quickly shift between relative reference and absolute reference when building formula; it also helps to repeat your last action when you are not building formula… so what am I actually talking about?
Continue reading
如何計算兩個日子之間有多少年／月／日？
Recently, I got enquired about function for calculating number of days from one date to another date. DATEDIF is on the top of my mind. ：）
Interestingly this function is not commonly known probably because it won’t show up in the formula bar, even you type exact the DATEDIF in the formula bar… I still don’t know why actually.
Continue reading
Learning Excel is a series of trial and error. Sometimes it succeeds, most of the time it fails. 🙂
Whenever I learn something new, I will test it again and again in order to make sure I understand the usage. However, if there is no real application in my daily work, I will forget it real soon. Then I realize that it’s not about learning a function or a feature; it’s about to link it to our daily work and turn it into real-life application.
To cite an example, when I first encountered OFFSET, I have no idea at all how I will use it…… until I learned about Dynamic Naming. Since then, OFFSET is one of my favorite functions!
Continue reading
如何快速轉換公式中的絶對參照及相對參照?
To kick off my first blog, I would like to introduce my buddy on keyboard to you – F4
To me, it helps
1) Switch between Relative and Absolute reference easily when typing formula
2) Repeat last action
Let’s talk about 1) first…
Continue reading
“A good teacher teaches; a great teacher inspires.”
Long time ago, I had a chance to sit in a class called “Decision Making with Computer” when my excel knowledge was limited to “table drawing” and basic + – * /. The professor showed some simple but amazing Excel spreadsheets that were really jaw-dropping. Since then my Excel journey begins…
The intention of this blog is simply to share Excel knowledge and tips. Hope you like it!
New Generation Finance - Accounting - Controlling using Microsoft BI stack
A Power BI Creator Blog
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Helping lawyers make the most of Microsoft Excel
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Peltier Technical Services - Excel Charts and Programming
Turn your data into opportunities
Q&A about Excel
Doug Glancy's Excel Site
wmfexcel 