Connecting Technology and Business.

Visualize Formula Linkages In Cells

​You workbook might contain many worksheets and each worksheet a lot of formulae. Or, you have received such a workbook from elsewhere. You are now interested in knowing how certain cells are related to the others in the same sheet or in other sheets. Excel has a very remarkable tool that will help you analyse this.

Under the Formulas tab of the ribbon you would see Formula Auditing cluster. This contains the necessary tools for this analysis.

First choose the cell that you want to analyse. This must contain a formula that connects to the content of one or more cells (which further can connect to other cells retrospectively or subsequently). Click the Trace Precedents command to see which cells link to this cells retrospectively. (Click Trace Dependents to see which cells link to this cell subsequently). Arrows are displayed to indicate the relationships of the cells. Boxes indicate an array of cells that are linked to the current cell. Click Trace Precedents further to see where the precedence goes until the original cell that determines the value of the current cell is reached through arrows. Precedent or dependent cells that lie in other worksheets or workbooks are indicated by a small worksheet icon at the tip of the arrow.

Trace dependents.png 

The figure above shows a sample worksheet with arrows of precedencies and dependencies of a selected cell.

Online Templates – A Treasury Of Readymade Workbooks

​Seasoned users of Excel (or any other Office Application) have always created their workbooks from scratch. The data as well as the formulae and functions that manipulate and present results in various worksheets of a workbook are entered by the users themselves. Today MS Office has evolved significantly. Online resources are overwhelming and help you save time and efforts of yours as well as others.

Office templates are mentionable resources that can not only create workbooks and documents quickly but can also prove to be very powerful learning resources as well. For instance, if an event has been planned in your enterprise. Assume that you have been given the responsibility of planning, executing and tracking the event activities and reporting the final outcome of the event. If you are an Excel user, help is just a click away.

As soon as you open Excel, you are presented with an option to open one of the many readily available templates that can be downloaded in just a few seconds and can be used immediately with no or little customization. (Or click New from the File tab). Click on the "Search for online templates" text box. Enter "Event". Excel searches for this and presents you with hundreds (at the time of my search, it was 467) of readymade workbook templates. I chose one of those and here is what I got: 3 worksheets – Expenses, Income and a Profit & Loss summary – with all formatted tables, formulae, graphs and summary report.

Event Budget - Workbook Template.png 

You can customize the format of the cells to show your local currency. You can rename the heads as per your requirement. And if you can spend some additional time to understand how the formulae work, then you can also add columns or rows as the situation would require it.

There are thousands of such workbook templates readily available for download and use by Excel users today. So, when you are required to create a workbook from scratch, wait a moment and see if you can benefit from this very valuable treasure trove.

Arranging Content In A Page Is Easy – Use Tables

I have seen users struggling to hold various contents at particular places. If the page has to hold a certain tables, some text boxes and graphics with title, headings and subheadings, quotes, notes and various such items, it is a real challenge to put them in the right places.

This can be addressed in various ways. The easiest is to insert a table with certain number of rows and columns to begin with. Then we can put each of the contents mentioned above in various cells of the table. The next step is adjusting the heights of individual rows, width of individual columns, merging cells or splitting cells so that the contents fits well. Filling the cells with suitable colours will give the contents a distinct look. There is no set rule to do any of these but experimenting with these will make you a professional page designer over time.

The final step is to remove the borders (or at least the unnecessary ones) so that the contents in the page all look naturally arranged in their respective places (but we know they are held in place by the table).

I am presenting a simple example of this here for better understanding.

Yammer eDM.png 

Electronic direct mailers, brochures, quick reference cards, datasheets and one page letters can be designed quickly and with much less effort using this method.

Use A Term In MS Office Documents With Confidence

You are in the middle of a Word document – say a report. You are trying to use a word that you though will be the best fit in that contest. But you are not sure. Does this word really mean what I think is its meaning? Is the tone expressed by the word right? Word is here to help.

Right click the word and immediately you are presented with one or more synonyms that will help you decide whether to use the word in question or to choose a weighty or lighter word in its place. It also helps you with one or more antonyms (if available in its installed default dictionary) so that you are doubly sure you are not using a word that conveys a completely opposite meaning than the intended one.

What happens if there are no synonyms to a given word in your default dictionary? (I was searching for the word 'Trove'). The Thesaurus comes to your rescue now. Word will look for an installed dictionary or help you download a dictionary (if not done earlier) to display the meaning. Many such dictionaries are available even free of cost in the Office Store today. (The audible pronunciation in US English and UK English is a bonus).

Fit Content in One Page

Often users a faced with a very knotty problem – unable to fit the last two or three into a page that overflows to the next page in a Word document. [There is also the other side, content not enough to fit a page but would look better if the page is filled]. Here are a couple of tips to overcome the overflow or the shortage. These are not put in a certain order. Use one or more of these tips whatever befits the requirement.

  • If the font size does really matter try changing it. Use Ctrl + < to decrease the size and Ctrl + > to increase the size 1 pt at a time. You might also want to try the .5 pt size – the middle ground between two full point sizes at the Font cluster under the Home tab of the Ribbon.
  • If the width of the margins can be compromised, then try the Narrow Margins or the Wide Margins option at the Margins command of the Page Layout tab.
  • You can also try modifying the spacing between lines depending upon the situation. Try the Line Spacing option in the Paragraphs cluster of the Home tab.
  • You might also try to Remove Space After Paragraph or Add Space Before Paragraph if you have formatted the page with paragraph headings.
  • If your file content is only one page, then, you might also try the Paragraph Spacing option in the Document Formatting cluster under Design tab of the Ribbon.

Cover Page For Your Report

You have worked for hours together to create a multipage report. You want to share this report with others. You are looking for a quick way to design a cover page for your report. Are you stuck somewhere? Here is a one click solution for your search.

MS Word provides a very useful feature for folks who are looking for a professional way of presenting your report.

Once your report is ready, click on the Insert tab of the Ribbon and click on Cover Page option in the Pages cluster which is at the left end of the ribbon. You will be presented with many cover page designs. Select the one that suits your requirement and then populate the fields. If you are not satisfied with the first selection of your Cover Page, you can go back to the same Cover Page option and Remove Current Cover Page and choose an alternative.

Some cover pages also let you include a picture or a graphic. This not only adds intrinsic value to the document but also makes it look attractive. You can also change the colours of boxes and fonts if you have time enough to play around.

The Proper Use of the English Underline

​Underlines are used to emphasise text. This has been inherited from the Manuscript days and typewritten documents. (It meant different things - A single underline meant Italics, a double underline (not showing up here properly) meant SMALL CAPS and a triple underline (can't show this here!) meant FULL CAPS and a wavy underline (neither here) for boldface).

Even we can do the same to the words, phrases, sentences or paragraphs in our documents using the options under the Underline feature of the Font cluster in the Home tab of the Ribbon. We can also use various colours for underlines to indicate various meanings.

When there are a list of proper names to underline in the same paragraph and we want to separate them word by word so that it does not get mixed up one with the other or wrongly related, we use a special type of underline – the Word only option. It is found at the More Underlines option at the Underlines dropdown. We can select the Words Only option at the Underline Style dropdown so that it does not include the space between words and clearly separates the words one from the other.

The Underline (called the Underscore _ ) character is sometimes used to create visual spacing within a sequence of characters, where a whitespace character is not permitted, e.g., in computer filenames, e-mail addresses, and in World Wide Web URLs.

Beware of automatic underlines that are wavy and coloured Red or Blue in Word documents. It indicates a spelling mistake or a grammar/punctuation mistake. Right click on the word and make use of the suggestions displayed.

Underline has altogether a different meaning in Chinese. Checkout the Wikipedia.

A Quick Monthly Planner

You are the beginning of a month and you want a monthly planner to plan your work activities for the month. Or, you want your team to submit a planner for the month so that you can keep track of their plans and align your activities accordingly. Here is a monthly planner for you in just one click.

Under the Insert tab, click on Table and Quick Tables and then choose the first option - Calendar 1. You will see that month's calendar displayed in your document. You can increase the size of the calendar as required and customize it with different font colours and an image as well.

December 2103 Monthly planner.png 

Strikethrough Scenarios In Mails

When can the strikethrough option be used in everyday documents? Many keep wondering whether this feature would be really useful at all. Think again.

You are forwarding (or replying to) an email. You are intent on discussing one or more points related to the original mail received by you. But you also want to draw the attention of the recipient/s of your mail to certain portions of the original mail while the rest is irrelevant to the receiver. (You also do not want to add a highlighting to the selected content which might lead to a confusion as to whether you added the emphasis or it was originally there). All the while, you also don't want the reader to lose the context in which the selected portions of the text were found. What do you do now?

You can strikethrough the content that is irrelevant to the point you are discussing in your mail. It preserves the complete context of the selected content and drives home the point quickly and well.

If you have another scenario for this this strikethrough feature, please share.

Do You Really Miss Your Good Old Toolbars?

​Users of 2003 would never forget the way the good old toolbars were useful while editing, formatting, drawing and reviewing their documents, slides and worksheets. But ever since the invasion of the new Office versions (from 2007 onwards), it has been quite a learning time for these users. Ironically, Microsoft spent millions to redesign the complete Graphical User Interface (GUI) and replaced the toolbars and menus (primary, secondary and tertiary) with the new Ribbon UI and with the new Fluent design concept. The main objective was to throw open all the features to the users that till then lay hidden under those toolbars and the dropdown menus.

(I learnt from a source that in a survey conducted among the users of the older Office to identify what new feature they would want to see in the new office that was being developed at that time (Office 2007), a majority of the respondents seemed to have mentioned one or another feature that had already existed in the older version. This was because they were not even aware of its existence).

But habits acquired over a period of time are hard to kill and adapting to change is a very difficult task for many. Probably, keeping this in mind, the designers of the New Office threw in a feature that would cater to the aspirations of the users who migrate to the new from their older office versions. Thus came into existence the "Quick Access toolbar"- QAT.

The QAT is by default found at the top left corner of the Office application's window and has a couple of options already checked. You can add more to the toolbar from the list that is displayed or add any command from the complete list when the "More commands" option is chosen. And if it starts encroaching upon the space left for the file name, it can also be shown below the Ribbon. Email is one command in the QAT that has been of great use to me. It saves me a considerable time and effort when I want to attach the current file to a mail. Just a click on this command will open a mail window (if my Outlook is open) and automatically attach the current file to the mail. Save the file with an appropriate name before doing this so that your attachment looks more professional (than just Document1 or Book1).