When we have created a wonderful #Excel template that is to be shared with others, we don’t want users to distort, if not destroy, the template someday. Nevertheless we would like to leave as much “flexibility” as possible to users by not password-protecting anything on the template. We know that some users are super creative to crack a password-protected sheet/book. 😛
It is simple to make a READ ONLY file. Continue reading
You’d searched all worksheets (visible or hidden and very hidden too) in order to locate the external links and remove them. Somehow, you just could not find any cells containing formula linked to external workbooks. You are totally lost as this warning message just popped out every time you opened the workbook. So the question in your mind… Where are the External Links?????????
Before locating where external links reside in your workbook, let’s answer the basic question first.
You see in the formula bar that the cell is referred to a worksheet called “VeryHidden”. However you don’t see it on the sheet tab.
Naturally you right-click on a sheet tab in order to unhide the worksheet “VeryHidden”.
You don’t see any “VeryHidden” in the Unhide sheet dialog box! 
How are we supposed to unhide a sheet that is not hidden??…. Wait, if it is not hidden, why couldn’t I see it on the worksheet tab?
Don’t doubt your eyesight. The sheet “VeryHidden” is hidden, that’s why you do not see it on worksheet tabs. Then why it is not found under “Unhide sheet”? Simply because the sheet is not only hidden; but very hidden.
Told by its name, a very hidden sheet is very hidden. It means it is very hidden. 😛
We cannot unhide it by the “regular” routine of unhiding sheet. We need to do it in VBE (Visual Basic Editor). Wait… Don’t be scared by anything starts with VB… It is super easy indeed. Continue reading
Wanna have fun with your colleagues who get too serious in #Excel on April Fool? How about playing Hide and Seek?
You may refer to my blogposts below for some ideas:
IMPORTANT: Play at your own risk! Especially when you plan to play it with your boss(es). 😛
Have you ever had this experience with #Excel? Someone sent you a workbook that is empty! What I mean empty here is not that there is no content in the workbook. You know that there are lots of data in the workbook but somehow you just could not see a single worksheet, row, or columns in the workbook. The following GIF demonstrates the situation:
The workbook is opened, with no worksheet. NO, not a single one. Almost all the icons on the ribbon were greyed out, meaning you cannot select them. Even though there a few, yes just a few, icons that you can click on… the next level icons are also dimmed.
Don’t be shy! Let silly things continue! Let’s Hide and Seek with #Excel again! 🙂
You may download a Sample File – Hide and Seek Cell Content for the above cases.
If you know the answers for all the three cases above, I believe you are good in using Excel. If you don’t know all of the above, you may want to continue. Continue reading
I know… it sounds another silly thing in #Excel. But I do believe many novice users have had this experience before. If you were one of them, you would know how frustrating that would be. I know, because I was one of them… ;p
Perhaps all we know, we need to select at least two columns (with the hidden column(s) in between) in order to unhide columns. Since there is no column on the left to column A, we may find it difficult to do such simple thing.
Indeed, there are many ways we can do to unhide Column A.
Making cell border in #Excel should be something real basic. However some people may have found it frustrating / confusing to play Hide and Seek with borders just hoping that they will be displayed in the way they want it.
For example, a simple table as below, we have Bottom Border on row 5 (or A5:B5 in our example):
When we hide row 5, the border line is gone….
It is hidden indeed. Did we just hide row 5? 🙂
Have you received workbook from others that carries underscore _ as if a space in their worksheet names? Did you wonder why people use underscore when we can actually use space in worksheet name?
Well… did you know… long long time ago, #Excel did not allow space in a worksheet name. If you knew it, you may think it maybe a habit for those who having been using Excel for years. Yes it could be… but I believe the people who stick to no_space_rule in naming worksheet indeed understand the advantage of avoiding space in worksheet name.
Many people don’t know what is the (subtle) difference, except the fact that one has space and one has no space on it.
You will see the difference when you need to write a formula to refer to a different worksheet, only if you pay close attention enough.
Now, let’s watch the following GIF to see if you can spot the difference? (Hint: Play attention to the formula bar) Continue reading
I seldom use the HYPERLINK function in #Excel. Normally I insert hyperlink by CTRL+K, then setting the reference I want the link to go to. That is super easy (or quick and dirty)!
Note: You may go to Insert tab on ribbon –> Link
However, laziness comes with a price, i.e. limitation ==> Static link. We cannot insert different hyperlinks based on the contents in a range of cell. And that’s the reason I need the HYPERLINK function. Here’s my story:
Recently, I created a workbook with more than 50 sheets. For easy navigation for users, I created a summary page, with a table of content. Well, 50 sheets! And I am not going to insert 50 different links by doing the CTRL+K 50 times. NO WAY.
Just a quick reminder that registration for the awesome Excel Dashboard and Power BI courses, by Mynda Treacy closes on Thursday February 15, 8pm in Los Angeles, to be exact.
With demand for Data Visualization skills and Excel data analysis jobs set to explode, now is the time to get these skills so you can take advantage of these exciting opportunities. Demand for workers with these skills is also predicted to outstrip supply, and that means these jobs will command a premium salary.
Mynda’s courses will have you up to speed quickly so you can start benefiting ASAP.
Dashboard reports, are no longer a buzz word, they’re a standard reporting tool for all kinds of industries and they’re an in-demand skill for Excel users everywhere.
In fact, IBM project that the number of jobs for all data professionals in the United States will increase by 15% by 2020.
Demand for Data Visualization skills alone is projected to grow by 44% and PivotTable skills by 34%. That’s great news for us Excel and or Power BI users.
Until recently Excel was the go-to tool (for most of us) for building interactive dashboard reports like the one below:
The benefit in having Excel Dashboard skills, aside from the kudos from your boss and colleagues, is that these skills are often transferable to your everyday Excel work.
How about an Interactive CV to show off your Excel skills!?
Once upon a time when I updated my CV, I was thinking…
Everyone states something like “Proficiency in Excel” in CV… ummm… probably someone would put the word “Highly” in front of “proficiency” with an intention of standing out from competition. Someone may even write a whole paragraph to explain what they do with Excel in plain texts; needless to say there would be lots of Excel jargon like VLOOKUP, PIVOT TABLE… etc.
While I was thinking how to describe my Excel skills in words, an idea flashed in my mind: “Why not showing my Excel skill in CV directly?”
That’s why I came up with this Interactive CV. By the way, it is so much FUN to do one, isn’t it?
Perhaps you have used VLOOKUP to return a value from a table very often. However you cannot use VLOOKUP to return an image… So how the above can be done? You will need to know three tricks:
You may download a sample file to follow along.
Let’s go through them one by one. Continue reading
Did you know that we can conduct online survey by using Excel? Yes, you heard me right. I said: Excel! I am not talking about setting up questionnaire in Excel and then send the Excel file to respondents. Of course we can do that, but are you sure you want to do the consolidation afterwards. I do not for sure!
What I am talking about is to create an online survey and then send a link to respondents to collect their responses. (Access to internet is, of course, required)
And the cool thing is… all the responses submitted will be loaded into an Excel table stored in your One Drive. You can imagine how many hours could be saved from consolidating the responses. 😛
So, how to create an online survey in Excel? Continue reading
Ho…Ho…Ho… Merry Christmas!
Wish you and your beloved ones a Merry Christmas and a Happy New Year of 2018!
The above is made of #Excel of course… You may download a copy Seasons Greetings 2017 if you like it. 🙂
Got the following question:
This gives me £28.8 K (if 28800 is input) and £28.0 K if 28000 is input. I was rather hoping that if there was no decimal after the thousand, ie £28 K, this would end up as £28 K, and not £28.0 K, but I guess that is not possible.
Here’s a screenshot illustrating the question:
Is it not possible????? Nothing is impossible, especially during Christmas. 🙂
Before we dive into the steps to achieve it, let’s see the custom formats for $28.0 k and $28 k first:
$#,##0.0, k --> $28.0 k $#,##0, k --> $28 k
See the tiny difference? Continue reading
Got the following question:
Suppose on column A I have entries and on column B i have due dates for the entries. Is it possible to have excel automatically rearrange the ROWS such that the nearest dates appear at the top and the furthest dates at the bottom? Thank you in advance
First thing on my mind: This can be done easily by sorting column B in ascending order.
Then I stop and rethink… maybe the reader wants to list the tasks that are not yet due… and probably on a separate table. And even more, new tasks with new due dates will be added from time to time and Excel should be able to get him updated list of tasks not yet due “automatically”… That makes sense and I believe it’s a common task for many people.
The following screen cast visualize the request:
And this is achieved with a simple helper column + Pivot Table. Continue reading
Can’t believe it is already December. Is learning Excel Dashboard one of your 2017 new year resolutions, that has not yet accomplished?
If so, here’s a good news for you: Mynda Treacy’s popular FREE Excel Dashboard Webinars are opened for the last time in 2017.
You could learn awesome Dashboard skills by watching the webinars alone. If you are serious about taking your Excel skills to the next level, you should really take time to learn more about Excel Dashboard.
If you’ve attended Mynda’s free Dashboard webinar then you’ll know how powerful Excel is and how having Excel Dashboard skills will skyrocket your productivity and career. If you want to acquire the Excel Dashboard skills, you may enroll Mynda’s course.
Loads of people have already registered and are on their way to wowing people with some killer reporting tricks, not to mention getting their work done faster.
So, go ahead and enroll here before the discount ends on December 7.
Disclosure: I make a small commission for students who join Mynda’s course via my site, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if it doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.
Excel School + Power Pivot Online Course Package by Chandoo.org
Disclosure: The author earns a small amount of commission (at no additional cost to you) for the product/service successfully sold through the links of [Affiliate].
Exploring open roads without breaking the bank
Work smarter by Mastering Functions in Excel
Chants of Power BI, Power Query and M
Work smarter by Mastering Functions in Excel
Leading Consultants on Power BI and the Microsoft Data Platform
Helping lawyers make the most of Microsoft Excel
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
zum Excel Profi werden
Transform your technical information for non-technical audiences through better data visualizations, reports, slideshows, and dashboards. I provide workshops, webinars, conference keynotes, coaching sessions, and custom design projects.
More geeky stuff from the author of www.excelguru.ca...
Work smarter by Mastering Functions in Excel
Excel Training, Education & Entertainment
Peltier Tech Excel Charts and Programming Blog
Microsoft Excel Solutions and Training
Q&A about Excel
Doug Glancy's Excel Site
A blog of Excel, Dashboards, Visual Basic for Applications, Data Analysis, Operations Research, and Visualizations.
Advanced Excel Techniques with XOR LX
Collection of Excel Know How and little Excel Tips and Tricks
Charles Williams on 'Making Excel go Faster'
Save Time and Money in Business
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Fresh Excel Tips, Power BI, Power Pivot, Power Query, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity
