|
|
|
|
|
|
|
|
|
| Quadra Blog |
9/7/2010
I was attending training on a new software product. It was scheduled for four hours. The compact training hall was house-full with about 20 participants. There was a large screen quiet close by the audience for any presentation to be projected on.
The trainer was sourced from a foreign country. He had a laptop computer that contained his presentation which he placed right in front of him and facing him. These made him face the audience as well as look at his computer screen during the presentation. He had a presentation that contained a lot of slides (a lot of slides, indeed) that the presentation could run indefinitely without end. All of these slides contained the same theme. It also contained static textboxes and some standard graphics that showed little difference between one and the other slides. Neither did they contain any slide transition methods. So the trainees could not recognize the changing of one slide to another resulting in us losing track of a lot of the content shared during the training. The worst part was that everything that the presenter wanted to share was displayed on the screen for us to read. And the trainer kept reading the content of the slides right from the first to the last from his laptop. As he had to read aloud the content, he made very little eye contact with us.
Those four hours were the longest four hours I have ever spent in my corporate life.
By the end of the four hour training we were given a two-hundred-pages spirally bound notebook and it contained the training material. Guess its contents? All the slides of the just concluded presentation printed, one per page.
Finally, in appreciation of our participation in the training, we were each given a complimentary gift – a USB pen-drive storage device. When inserted into my laptop's USB port for first time use, I found something more to my surprise / frustration. It contained the very same PowerPoint presentation that was read to us on the training day.
Now, what is the moral of the story? Not just one but many. Let us list them down:
- Limit the number of slides per presentation. (Ten slides including the title slide and the summary slide per thirty minutes of slideshow might be a good thumb rule).
- Display your message in points wherever possible on the slides
- Use bullets and numbering to make your points retention material
- Use not just text boxes but also colorful graphics, pictures, photographs, charts, audio and video that will help you drive home your points powerfully
- Use appropriate animation to the slide elements
- Use a slide transition scheme to clearly demarcate sections that contain different ideas presented during the show
- Show captured web pages, pdf documents or excel sheets and word documents (if they are not confidential) as credentials to your point of view during the presentation
- Don't just read aloud the content of the slides during the presentation
- Have more material to share verbally and visually than the content in the slides
- Pitch in with a live demo of your product if possible
- Use the notes area to type the information that will help you explain the slide contents
- Use the presenter view in PowerPoint 2007 / 2010 to view the notes and the slides at the same time during a slideshow
- If you are sharing the presentation as a softcopy with the participants, there would be no need for printed handouts of the slides
8/26/2010
What would you do if you are asked to send one mail to multiple people? And here is a catch - you are required to send this mail addressed to each one of them (their name mentioned in the mail). Well, you are given a list of these recipients (with their email addresses and other information), thankfully, in an electronic format - in an excel file.
I got introduced to the Mail-merge tool when I was using "Word Star" I the 1990s; but the power of this tool has not been understood or put to use by users even today while it has been available to them in MS Word in from 1997. Yes, you have to start from MS Word, not from MS Outlook! Today, MS Word offers a very user-friendly interface to merge your mails with your MS Outlook Contacts or with your database created in other applications.
I am presenting this post for those who want to know how to mail-merge– Use MS Word to create just one mail, customize this mail for individuals at one shot and send them through MS Outlook using a database that you have created in MS Excel that contains Names and Email addresses of the respective individuals apart from other data fields.
To begin with, you must have a database table in Excel that contains essentially Names and emails addresses of the mail recipients. It might also contain other details like their designation, company name, address of their geographical location etc., which you might want to include in appropriate places of the mail. Each of these field data must be in separate columns and each individual's record arranged in successive rows. The columns should have definite column headers. For e.g., the columns that contain the First name of the mail recipients should be named First Name and the column that contains the recipients' email addresses should be named Email. I am going to use only the First Name and the email address columns in the current example for mail-merge to make it simple.
We will now open a new MS Word 2007 document and go to the Mailings tab of the ribbon. For beginners, it is better to open the Step by Step Mail Merge Wizard from the options that drop from the "Start Mail Merge" command, which will open as a pane in the right edge of your Word window. After you become familiar with the mail merging tool, you can just go about this activity even without the wizard's help by just choosing the commands in the ribbon under Mailings.
We will first "Select document type". You will see that not only email (which is our present example) but also other documents types can be mail-merged using this tool – Letters (Hard copy /printed letters), Envelopes, Labels and Directory. We will now select "E-mail messages" as our choice of document. This is Step 1 of 6. We will click "Next: Starting document".
We will now use the current document to type the email afresh. (If we already have an email ready in a word document, we can open this or if we have an email template stored in our system, we can open this template and then go ahead). This is Step 2 of 6. Click "Next: Select recipients".
We will select recipients using an existing list. (You can choose the recipients from MS Outlook contacts (as mentioned earlier) or type the list real-time one after the other right here). We need to show where the Excel file with our list of email recipients is located using the "Browse…" command. On selecting the Excel file, we are shown all the worksheets in the workbook from which we need to indicate which worksheet contains our list. Immediately, the complete list is displayed, allowing us to refine the list with different options at the bottom of the dialogue box and also letting us uncheck a name from the list (if we choose not to send this mail to the respective person). This is Step 3 of 6. We are halfway through. Let us click "Next: Write your e-mail message".
Most of the time, we might want only a "Greeting line" in our emails and not the "Address block". If this is the case, we might as well skip that step and add only a "Greeting line". If the displayed sample does not contain the information that we intended even though the Excel table contains all of those, we might have to "Match fields" – map the appropriate Mail-merge fields with the column headers in our table. [We could have also added the "Address Block" (which contains the details from the list we have chosen – First Name, Last Name, Address 1 and Address 2 and other fields) depending on the options we choose in the check boxes at the left of the current dialogue box of the wizard in the same way we added the "Greeting line"]. The option "Electronic Postage" is irrelevant to our country (and to our email messages), so let us ignore this. You can also add more items (column information like a date or time for e.g., of interview or any other data that is available in our list) - in the mail to make it more customized. Experiment with them later on. This is end of Step 4 of 6. "Next: Preview your e-mail messages".
Here, we can browse through the individual mails with the custom information displayed in each of them. We are also presented with an option to exclude any recipient that we think is worth removing from the recipients' list now. (If it were Letters, we could have also edited individual letters, if necessary). This is the penultimate step – Step 5 of 6. "Next: complete the merge".
We will now merge "Electronic mail…". We are now shown a dialogue box with the "Email" field (if correctly matched earlier) in the "To" box, a text box to enter the subject line of the mail and the option to send the mail in HTML or other formats. We are also allowed to send the mails to all selected recipients or to the current recipient only or to a series of recipients in the list (from record no. m to record no. n). This is the last step – Step 6 of 6.
We now see all these mails filling our Outbox in MS Outlook and moving out of it one after another (if we are online and connected to the Exchange) and start getting listed in our Sent items folder.
You can use this Mail merge tool to send electronic direct mailers (eDMs) to customers, electronic meeting invitation to prospective attendees, Interview mails to prospective job seekers and a lot more instances where you are required to customize the same mail a hundred or a thousand times. You can also print customized letters on paper at one shot or generate lables. Refer to one of my other blog postings on labels generation - "Create Address labels the easier way!".
Believe me - We can save a lot of time - really a lot of time, when we become familiar with this tool. 8/13/2010
How many times were you forced to open another Excel workbook to copy a list that it contained into your current workbook! Also think about the time you were trying to recollect the workbook's name and the location where you saved this workbook. And finally after successfully recollecting the name of the workbook and tracing its location, when you opened the workbook, you found to your dismay that the list that you intended to copy was not there but it contained another one! And you were back to square one – Started searching for the right workbook. What if you had many such lists that you use very frequently? Is there a way you can ask Excel to remember each of these lists? And display them wherever you wanted and whenever you wanted?
Do you know that Excel already remembers a few such lists called Custom lists? Weekdays, Months… All you did was typed the name of a Weekday and used the Autofill option to fill a series of cell in a row or a column with other weekdays that followed the first entry. Similarly, you could also fill names of months. Well, we can use this feature to build our own lists in Excel and use them the same way we use the examples sighted early in the paragraph. Let me show you an example of how to create your first custom list.
First, you must create a list with the entries intended to be included in it. You can have a list of names of your colleagues, departments, projects, facilities, locations, products, ingredients of menus and what not. Make sure that the list is in either one row or one column. (If you already have a readymade list in one of your Excel workbooks, well and good. Just open the workbook). Select the range that contains this list. Now, click the Office button (top left corner of your Excel window) and choose "Excel Options". Immediately on the right pane of the options, you will see the "Edit Custom lists" button. Click this and it opens a dialogue box. You see the range already selected at the "Import list from selected cells" Text box. Click "Import". Immediately, the whole list gets imported to the "Custom lists" List box. The individual entries in the list are listed to the right of this list box as "List entries". Click "OK" and "OK" to close the successive windows.
Your custom list has now been saved in your Excel application and can be used in any workbook that you now have or will open hereafter (in your own desktop/laptop, of course!).
When it is time to list the items from your custom list in a worksheet, follow the general procedure – type any one of the entries in the list and then Autofill the rest of the cells in a column or a row. (If you fill more cells than the number of items in the list, the list will keep repeating its items). If you type the first entry in all caps (or all lower case or as a Proper noun case), Autofill will follow suit.
If you don't want to even remember the items in the custom list, add a generic name to the list items to identify the list. Say, if you have a list of fruits created, also type "Fruits" as another entry in the list. Thereafter, you need to just type "Fruits" and then Autofill.
You can also edit this custom list later on – add, remove or correct one or more entries in the list. Just open this custom list from "Excel Options" and go ahead.
Using custom lists saves your time (searching for the list, copying and pasting), helps you avoid spelling mistakes (provided you did that right the very first instant) and remembers thing for you so that you don't have to memorize the list items. 8/9/2010
A pie chart is plotted for data that is arranged in one column or row only on a worksheet.
Pie charts show the size of items in one data series proportional to the sum of the items. A data series is related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series. The data points in a pie chart are displayed as a percentage of the whole pie. Data points are individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.
Consider using a pie chart when:
- You only have one data series that you want to plot.
- None of the values that you want to plot are negative.
- Almost none of the values that you want to plot are zero values.
- You do not have more than seven categories. (More meaningful)
Display the share in percentage
You can display the values of each data point as data labels in different ways. The original values, when displayed as data labels will convey very little meaning. It is better always if the percentage share of each of the data points is displayed. You can also display both the percentage share and the original values if the situation requires them. You need to go to the "More data label options" feature to do this.
Display a relevant picture
You can also make the portion of the pies more meaningful by displaying the pictures of the data points. You need to first select a data point with a double click. Now you will see the three corners of the data point marked. Again, with a right click, choose the "format data point" option and then fill the data point with the respective picture from a file. Repeat the same procedure to display picture of each of the other data points.
Display a highlighting
You can also highlight a portion of the pie chart by pulling out a little this part only. You need to select the data point first with a double click. (You will see the three corners of the data point marked). Now, you can just drag this portion of the pie a little away from the rest of the pie. Your data point is highlighted. The actual benefit derived from the feature is diminished as you pull out more data points from the pie. (You don't highlight most of or a whole paragraph if you have only one paragraph of information, do you?)
Display the pie in gray scale
If you intend to print this pie chart in a paper using a black and white printer, consider showing the graph in gray scale before printing it. Else, some of the colours in the pie will look identical and cannot be differentiated. You can revert back to colorful looking chart schemes in the gallery later on if this is to be used electronically in Excel or PowerPoint or as a pdf file. 7/27/2010
Have you ever faced a situation where you wanted to send a screen shot to the recipient of a mail? Or you wanted to pick a portion of the screen as a screenshot and use it elsewhere – in Word or PowerPoint or PhotoShop?
You always thought that the Shift+PrtSc way was the only way to do this. But there is an easier way of doing this if you are using Outlook 2010 or Windows 7 or OneNote 2007/2010.
In Outlook, under the "Insert" tab of a new mail, you will see the "Screenshot" feature. All you need to do is click this and you will be presented the various windows that are already open and minimized on the taskbar. (You need to have the respective window or page open to take a screen shot of the same). Point to the Window for which you need the screenshot and click. Immediately you will see the screenshot pasted in your current mail's body.
In Windows 7, you would see the snipping tool under the accessories which will help you pick up screen clippings from the currently open window. When you invoke this feature from an open window, you have options of clipping the open window – freeform snip, rectangular snip, window snip or full screen snip. When you select one of the first two options, the whole window goes hazy and you need to select the portion of the screen that you wish to clip. The "window snip" option will present you with an option to point to the window of your choice from among the open windows. The "full screen snip" option captures the whole screen with all the open windows. The snip is copied to the clipboard and can be emailed or pasted in any other application. If you want to add comments on the screen clipping, you can still do it from the snipping tool window. You can also save the clipping in different file formats – HTML, PNG, GIF or JPEG.
If you are using OneNote 2007 or OneNote 2010, you can copy a rectangular snip from an active window to one of your OneNote Notebooks or to unfiled notes. You can also do this using a shortcut key – Windows key+S.
Now, picking up screenshots is not so difficult as they used to be, is it?
Have you ever wished you were able to view two or three pages of the same document at a time? Or view two or three worksheets of the same workbook at the same time instead of flipping through the worksheets on their tabs multiple times using the mouse (or use Ctrl +Page up or Ctrl + Page down)? Here is help…
You need to go to the View tab in Word or Excel and there you will find an option "New Window". Click that option once to open another instance of the same file you are working on. You might have to click "New Window" again if you want a third instance of your file. After this you will have to click "Arrange all" and use the "Vertical" or the "Horizontal" option as your requirement might be and you will see two windows (or three, if you had opened two new windows), all arranged on the same screen. In Excel, you will see an additional option when you request for "Arrange all". You need to choose "Windows of active workbook". Now, you can jump to the second or third window and navigate to other pages of your document (in Word) or worksheets of your workbook (in Excel).
You will save your time. Comparison between pages or worksheets will be easier and error free. Any change you make in a window is essentially a change made to the same document. So, you don't have to save the file multiple times. After closing all windows but one, you can save your file and that is it. 6/21/2010
Did you know that you can do Excel in Word? Read on…
Whenever there is a need to insert a table that contains extensive calculations and complex formulae in a Word document, it is always the best to use the Insert -> Object -> Excel Workbook option. This is better than inserting a table from the "Tables" feature of the Home tab in the ribbon. This option would also be the preferred one if you want to sort or filter the data in the table.
True, the Word table (the second option mentioned above) will allow you insert some very basic formulae like "=sum (above)" or "=product (left)" from the "formula" feature under the layout tab (contextual) under "Table tools", but it does not allow you to include formulae in MS Excel fashion – with extensive mathematical operator combinations and functions with relative/absolute/mixed references. Nor will it allow copying of formulae from one cell to the other by way of copying and pasting or drag-copy of the formula from one cell to its adjacent cells – up, down, left or right.
In the Excel table that is inserted as an object, you can do everything that you would do in an Excel sheet as this is the very same Excel that got installed when you installed the MS Office suite. You will see the Excel ribbon displayed as soon as this Excel workbook object is inserted in the Word document. You need to double click inside the table when you have gone away from it to work in it again or display the Excel ribbon.
A word of caution though - if you have only installed Word and excluded Excel right at the time of installation, then you cannot expect this functionality in Word. 6/14/2010
You can make use of a lot of powerful keyboard shortcuts during a PowerPoint presentation to make it a professional one. Here are ten tips I thought would be useful to all presenters.
- Start a presentation from the beginning – F5 – this might be of use if you are going to start all over again (Did some important member/s of the audience walk in late?).
- Resume a slide show from a given slide – Shift + F5 – this, when you did some editing in the slide and want to resume the show from there (Starting from a specific slide in an uncustomized presentation?).
- Perform next animation in the current slide or move to the next slide – N, Enter, Page Down, Right arrow, Down arrow or spacebar (Stopping for a while to deal with the point in focus?).
- Perform previous animation in the current slide or move to the previous slide – P, Page up, Left arrow, Up arrow or Backspace (Did somebody want to look at the last animation/slide again?).
- Go to a certain slide number – Slide number + Enter – eg. to go to 5th slide – 5 + Enter (Want to jump forward / backward in the presentation by skipping some slides immediately?).
- Display a blank black slide – B or period (.) – might be useful when you want to temporarily suspend the presentation (Allowing a tea break?).
- Display a blank white slide – W or Comma (,) – might be useful when you want to suspend the presentation momentarily but still need enough lighting in a dark room (Answering a query?).
- End a presentation – Esc or Hyphen - (Want to finish somewhere in the middle of a long uncustomized presentation?).
- View the computer taskbar – Ctrl + T - (Have some open files – word/excel/PowerPoint/drawing/pdf to show during the presentation?).
- See these and other controls displayed on the screen during a slideshow – F1 – (Forgot any of the above?).
6/8/2010
Let us assume that we have a data table like the one displayed below:
This table might be a database with thousands of such records and scores of fields per record. Employee Number is the primary key (field that contains unique entries).
Now, I want some selective information about a few of the employees from this database. Say, I need info about the employees with given employee numbers and their scores in the 2nd quarter of the calendar year, i.e., April, May and June.
I can now use the power of VLOOKUP function to fill this information in my target table immediately.
VLOOKUP need three compulsory arguments and one optional argument to look up information vertically from the source data table.
Here is the dialogue box if you are going to use it but I will let you know how to use this formula directly in a cell.
We will point to the cell to the right of the first name – Bright – and start typing the formula beginning with an equal mark. =vlookup(. We need to tell Excel what to look up – the lookup value. Now, it is employee number – A003. Note this is a unique value from a primary key field.Next, we will indicate the columns that contain the source data table. It is A to I in our case. Then, we need to tell excel the number of the column from the primary key field that contains our required information. It is the 7th column – April – in our case. We will type 7 as the third argument. Then we will tell Excel to pick out the info only if there is an exact match for the lookup value in the source table. So, it is a 0 (zero) which will be last argument. Close the formula with a bracket to complete the function and the arguments and press Enter. The cell with the formula will contain something like this - =VLOOKUP(M2,A:I,7,1). You will see the result being displayed immediately.
You can auto fill this formula in the cell below this cell to display the respective results.
The formulae in the cells are displayed hereunder for further help.
(You need to make a few changes in the cell referencing in the formula in order to copy this across, and do another minor change in the formula in every column, which is beyond the scope of this current blog posting. We will deal with this later).
You have to be very careful with some of the details while using this function. We will deal with this in the next posting. 6/1/2010
Assume that you have a database. The database contains scores of fields and thousands of records. Also assume that you need to extract information from about a hundred records of the database into a new worksheet. You also don't need all of the fields but only a specific set of fields. What do you think would be the time required to collect all this information? Certainly a lot of time indeed! (Unless you know a very easy and effective method of handling this requirement…).
Here is where vlookup would come of use.
If you need to look up a database vertically for multiple fields of information, then the vertically look up function (vlookup in short) will help you extract the kind of info mentioned in the first paragraph. I will let you know how this is done in the blog posting that follows immediately after this one.
Meanwhile, users who are already using this function, kindly let me know if you are facing any issues while using this function and I will address them in the blog. |
|
|
|
|
|
|
|
 |
|
|
|
|