Navigate the Ribbon easily!

Hello again!

This post is specifically intended for Microsoft Excel/Word/Powerpoint Users who are using the 2007/2010 edition of MS-Office.

If you are one of those old timers (like me!) with the Office Suite, you may not be really up to speed with finding the right commands in the new Office Ribbon which replaced the earlier Menu bar model. If you are one of those newbies to using Office, the problem is even worse because you may not even know the shortcuts to the right keys or commands which the old timers know and use as an alternative.

So, what is the way out to learning more about the Ribbon and using it well? Two options:

a) Learn it the real hard way - try exploring and memorizing, use the Help feature or Google up for finding the right commands and right spots to pick them up.
b) Go the easy route - as explained below!

Ok, here is the easy route (and less known too). Microsoft published a special tool for all users like us who were not very familiar with the Ribbon to help us easily search for commands and identify them with the Ribbon - to help us eventually find the right place and position, so we can become experts with the Ribbon.

Here is how this add-on will look like and behave once installed in your system:

Though this free add-on was published way back in 2011 and was aimed largely at the 2007 / 2010 editions only, Microsoft is still keeping the product available for download from its Office Labs section. If you are interested to download and use this simple utility, please visit Microsoft site on this link: https://www.microsoft.com/en-us/download/details.aspx?id=28559 

Helpful warning: Do not try to download or install this utility with any other version of Office like 2013  - it may not even install. Stay away from this and save your time!

Hope this helps. Let me know if you have any questions or have any additional suggestions.

You can learn a lot more about the power of Defined Names and get interesting insights into the power of Microsoft Excel from my books on Excel. Check out the following:

Kindle Versions:

Excel for the CFO -http://www.amazon.in/Excel-CFO-Professionals-P-Hari-ebook/dp/B00AH4ZWEI/
Excel for the CEO - http://www.amazon.in/Excel-CEO-Professionals-P-Hari-ebook/dp/B001E67E0S/

Paperback Versions:

Excel for the CFO - http://www.amazon.in/Excel-CFO-Professionals-P-K-Hari/dp/1615470115/
Excel for the CEO - http://www.amazon.in/Excel-CEO-Professionals-P-K-Hari/dp/1932802177/

Ebooks:

Excel for the Small Business Owner: http://www.mrexcel.com/sbo.shtml
Excel for the CFO Ebook - http://www.mrexcel.com/store/index.php?l=product_detail&p=243

Throwing Names around!

In the real world, we are so used to people "throwing names around" to get things done or to convince others that they are all powerful or that they are in touch with very powerful people.

I also advocate throwing names around - certainly not in the above manner, but in a more productive way, throw names at Excel and exploit the power of convenience and clarity that Excel provides you in return.

Take a look at the below figure to get a better idea of what I mean:

Look at the Formula Bar which reflects the formula contained in Cell C2.

Something striking? Yes, you see that the formula does not contain any cell references but is more of simple English text, which would appeal to any common user who does not have great working knowledge of Excel formulas. Anyone looking at Cell C2 can easily deconstruct the working of 4,000 and tell you that it is a multiplication product of Deposit value and Interest rate.

Practically, the technique of writing such named formulas come handy if you are constructing a large workbook with multiple variables stored in different corners of the file which make it difficult to go back and refer to the cells. Alternatively, if you are building a file which has to be very simple to understand and is intended for the not-so-advanced user, this is again a helpful thing to do.

Now if you have the question of how to go about writing such named formulas, it is pretty simple - just follow this sequence:

• Name the cells that you want to use in a Formula. There are multiple ways to doing it - simplest way is to click on the cell that you need to name and then click in the Cell Address Box (for instance, look at the small box in the above figure carrying the word "C2" just to the left of the Formula bar). Technically, this drop down is called as the "Name Box".

• Now you would be able to just block out the existing cell address ("C2" in above example) and type any name that you wish to give - something that is descriptive or indicative of what is contained in the cell. Press Enter and you are done with the naming. Let's presume we give the title "YearlyInterest" for this Cell C2 in the above example.

• Now you are free to use the new name in any other formula within the same workbook. If you want to compute Monthly Interest in Cell D2, you would just enter this formula "=YearlyInterest/12" and it would automatically reference back to Cell C2 and divide that by 12 to get you the result.

Defined Names is one of the most powerful tools available for the modern day analyst and every other Excel power user - so get to learn and start using it as much as you can. You will find it very convenient and handy.

Remember, you can also use the name boxes to create names in local languages too and reference them in the same language elsewhere in the file. Just to give you some flavor, the above example can be written up in Tamil as below (this could appeal to users who would understand local languages better than English and complex formulas):

If you are thinking of how to type in the local language and create such naming, use the Google Transliterate tools that caters to almost every popular Language.

Hope this helps. Let me know if you have any questions or have any additional suggestions.

You can learn a lot more about the power of Defined Names and get interesting insights into the power of Microsoft Excel from my books on Excel. Check out the following:

Kindle Versions:

Excel for the CFO -http://www.amazon.in/Excel-CFO-Professionals-P-Hari-ebook/dp/B00AH4ZWEI/
Excel for the CEO - http://www.amazon.in/Excel-CEO-Professionals-P-Hari-ebook/dp/B001E67E0S/

Paperback Versions:

Excel for the CFO - http://www.amazon.in/Excel-CFO-Professionals-P-K-Hari/dp/1615470115/
Excel for the CEO - http://www.amazon.in/Excel-CEO-Professionals-P-K-Hari/dp/1932802177/

Ebooks:

Excel for the Small Business Owner: http://www.mrexcel.com/sbo.shtml
Excel for the CFO Ebook - http://www.mrexcel.com/store/index.php?l=product_detail&p=243

Test the TEXT function!

We will learn something interesting today about a lesser used text function, which is called “TEXT” itself!! Can you ask for an easier name to remember a function? 

Ok, let us get on with the brilliant features available with this function.

Syntax:

=TEXT(value, format)

Where value is the value to convert to text, and format is the format to display the results in.

Format can take one of the following modes – number, date, time, currency, percentage, scientific notation and finally the default text format too.

This function is very rich and powerful with all these different formats and notations, and it would be easier to understand the use of this with some practical examples, than with boring text. 

Look at the following image to understand the concept better.

Example:

Practical tips:

In a real life scenario, you might come across a situation where you get some values in different formats, for instance, the date might get populated as a text and come to you from a database. In such situations, this function can be very handy as you can use a combination of standard set of DATE functions, the LEFT, MID and RIGHT functions to extract the date strings and use the TEXT function to output it in the desired format. 

Similar applications of this function is virtually unlimited and the scope would be as large as your ability to imagine solutions out of this wonderful multipurpose utility function.

Download link:

The example shown above can be downloaded and viewed as well - the sample workbook is stored here for your ready reference - http://sdrv.ms/WiHRNq 

My upcoming book on Excel contains many more such tricks and practical insights into getting Excel work for you. Please take a moment to visit my earlier post to understand details about my new book, and make use of the special offer available now to order it at a discount.

PivotTables – using the Summary functions

Today, let us learn a bit about PivotTables and the convenient assistance they offer us in many ways. This tip is based on a special request from one of my friends P Sreenath, who wanted me to write a few things on Pivots.

Let us take an example to understand the summary functions better. Consider the following table of student and subject-wise academic performance (data of marks scored):

Now, the school management wants to do a summary analysis by subject and find out the maximum marks scored, the minimum marks scored and the average marks scored, so that they can evaluate the respective subject teachers. 

Since the data is available by student, if we need to get the summary level data by subjects, we must sort the data by subjects and then use the MAX, MIN and AVERAGE worksheet functions on each data range to get the values of maximum, minimum and average scores respectively - see the restructured sample below.

However, there is an easier option available and would be a boon especially when you have a huge number of records to work on. You can forget the manual methods of sorting and applying formula to each selected range of subjects and three different forumulas for each one – in this current example, 4 subjects x 3 functions each = altogether 12 formulas to be setup manually. 

Take recourse to the PivotTables, and let us find an easy solution. Just follow these steps:

Step 1: Select the data range, and click on the PivotTable button on the Insert menu of the Ribbon.

Step 2: Choose your option of New worksheet or existing place in the current worksheet, to get the PivotTable skeleton structure.

Step 3: Drag the Subject field to the Row Labels zone, and drag the Marks field thrice to the Values zone – as shown below:

Step 4: Now click on the first Marks field (“Sum of Marks” in the values zone) and select Value Field settings. In the dialog box, just select the function MAX (instead of the default sum) and click ok.

Step 5: Repeat the process on “Sum of Marks2” – making it MIN and “Sum of Marks3” – making it AVERAGE. Your work is done in a jiffy now. See the summary results below.

Note: 

When you do the average, you will note that there are too many decimals that appear. If you want your worksheet to look clean and without decimals, you can use the value field settings again on that field, select the Number Format button which will bring up the Cell properties dialog box. You can set the field to Number type with zero decimal places there and click OK. Note that the number formatting would be retained on the PivotTable only when you set it through the Value Settings dialog box. If you use the comma button or $ button on the Ribbon to format the cells, the formatting would be lost the next time the Pivot is refreshed. 

The example file is available on Skydrive for you to take a quick online view  - http://sdrv.ms/SsLOOa

Hope you will find this tip useful and handy. I will try to share few more interesting tips on PivotTables in the coming days as well. Would be glad to have your comments on this, and help your friends also to learn a trick or two – share the blog details with them!

My upcoming book on Excel contains many more such tricks and practical insights into getting Excel work for you. Please take a moment to visit my earlier post to understand details about my new book, and make use of the special offer available now to order it at a discount.

An undocumented Excel Function!

I know it could be boring for you to learn about Excel functions, which are available in plenty, and on which Excel Help offers good insight. So, I chose to write about an Excel function today that exists and can be used but on which Excel Help has nothing to say.

You would know that the default way to find difference between two dates is to just use the subtract formula and format the result cell as number. For instance, if Cell A1 contains startdate and A2 contains the enddate, you would enter the formula “=A2-A1” in the result cell, and format it as number to see the days.

Today we are going to learn about a date related function, which is an undocumented Excel function – and is actually a nice utility for computing the difference between two dates. Beauty of this function is that you can get the difference expressed in days, months, years or a combination of any of these. The function is called “DATEDIF” and can produce some unimaginable (positivelyJ ) results, as we would see in the examples that follow.

Formula Syntax:

=DATEDIF(startdate, enddate, interval)

Here, the startdate  and enddate would just be cell references (like A2, A3) which hold the respective dates for which you want to compute the difference. The last parameter, interval, can take one of the following forms (include the quotes):

• “d” to get the result in number of days
• “m” for number of months
• “y” for number of years
• “ym” for number of months (ignoring the year)
• “yd” number of days (ignoring the year)
• “md” number of days (ignoring the month and year)

How it works:

I believe in the saying “A picture speaks a thousand words”. Take a look at the below screenshot which I have constructed for you - it would make things very clear than any number of explanations.

 Deploying a combination formula:

You can obviously make use of this function and deploy a combination trick to get the exact number of years, months and days neatly in your result. See the following picture to understand this – am giving you two variations, the second one would look more neat because I use an additional IF function to take care of the Zero value situation.

That completes the trick for the day. Spread the word around to your friends, if you liked this and find it to be of some use.

My upcoming book on Excel contains many more such tricks and practical insights into getting Excel work for you. Please take a moment to visit my earlier post to understand details about my new book, and make use of the special offer available now to order it at a discount.

My new book - now available at a special price!

Let me admit - am pretty impressed with the number of hits on this relatively new blog, and am very thankful to all of you, readers. As a special measure of thanks, am glad to present you a hard-to-resist offer - an opportunity to order my upcoming book at a whopping discount of 34%.

Yes, you read that right - 34%, but it may not last for too many days. Amazon has opened up this special pricing just for a few days, and if you are interested to get my book, please place your order using the below links, at the earliest.

Here are some details about my new book "Excel for the CFO" - slated for release by 1st November, 2012.

Brief of the title:

Written specifically for finance managers, Excel for the CFO explains the best features of Excel that allow for the automation of regular processes and help reduce the processing time spent on analytics.

The book explores the entire gamut of finance-related functions and is focused on practical approaches to using Excel—including Pivot Tables, Goal Seek, Scenario Builder, and VBA—in problem solving to deliver quality results.

Using case studies across all types of organizations to demonstrate the application of Excel-based automation, the scenarios covered include the automation of financial analysis models, the creation of income statement and balance sheet templates, converting numbers to words for check printing and much more.

Any finance executive who manages the company’s business affairs and makes critical decisions by analyzing data would be directly benefitted by using the tips and techniques presented in this guide.

Pricing:

List Price            : $29.95
Pre-order Price   : $19.77
You Save            : $10.18 (34%)

Amazon Link:
http://www.amazon.com/Excel-CFO-Professionals-P-Hari/dp/1615470115

Incidentally, my other books are available with some good deals. Take a look at these links:
Excel for the CEO - Print edition
Excel for the CEO Ebook (Kindle edition)
Excel for the Small Business Owner (Ebook)

Is it possible to copy Page Setup?

Many of us would have faced this situation - where we take a lot of time to do some specific page setups for a worksheet, and later realise that we did not select the other sheets while doing the setup.

The result? - you would have to do the whole thing all over again, because Excel does not offer a regular menu option to copy the page setup features. We have all sorts of copy-paste, including format painters but when it comes to Page Setup, none of those come to the rescue.

Never mind, trust the experts to always come up with an easy solution. Here we have a simple solution for this complex problem - one of the most appreciated tips from my book "Excel for the CEO".

Problem Statement:

Apply the specific page setup settings for one worksheet to the eight other worksheets in the same workbook.

There is no built-in option to do this in Excel.

Solution steps:

1. What you need to do is very simple – first place your cursor in the sheet that has the page setup you want to use.
2. Now select the sheets where you want to apply the page setup using the mouse cursor while holding down the Ctrl key. 
3. If you are using Excel 2003, select File → Page Setup from the Main menu and just click on OK. 
4. In Excel 2007 & later, Page Setup is available from the Page Layout tab of the Ribbon. Alternate is to select File->Print and then click on the small Page Setup button available under this. 
5. That’s all – your page settings are automatically transferred to the eight new sheets.

Note: Some of the tips shown here are extracts from my book on "Excel for the CEO" - details available at www.mrexcel.com/ceo.shtml. You can also find this in the ebook edition "Excel for the Small Business Owner" available for online ordering at www.mrexcel.com/sbo.shtml