Sunday, February 22, 2009

Pasting Link?

I like this feature a lot. Here is a spreadsheet with figures and also a bar chart of the figures, which in this case is the yearly expenditure for printing in the company.

Now, assuming that I have a presentation to deliver, and I need this chart to be in one of the slides, I would typically, copy the slide (right-click + copy or Ctrl + C)

Ok my presentation deck is as shown, and I would like to place my copied chart in the Analysis slide here. I click on 'Paste' (Home tab) and choose 'Paste Special'. The dialog box opens, and I choose to Paste Link, the screen tip explains that Paste Link creates a shortcut to the source file so that any changes that occur will be reflected in the slide. Your chart is now in the slide you selected.

Back to the source file, let's check whether changes will be automated or not. I have just ammended the value for 2008, from '5605' to '7005'.

In powerpoint, right-click the chart, and choose to 'Update Link' and the change is reflected! Therefore if you have correctly pasted link for your charts, etc, when the values in the source file changes, these charts in your powerpoint slides will be updated when you open your deck.

Let me continue with my presentation slides. This presentation is about encouraging the company to 'Go Green', so right now I would like to visualize how one of the business processes is being done currently, and unfortunately it utilizes high amount of paper.
I remember seeing a diagram in an existing slide, so instead of redrawing it with shapes, text boxes, and icons, all I need to do is go to the Home tab, click on New Slide, and opt for the 'Reuse Slide' button.

A side pane will appear on the right, and here I will specify the path of the deck in which the diagram could be found by clicking on Browse > Browse File.

When you hover over the slides, you get a magnified view of it, and by clicking on it, the slides gets inserted into you current set of slides - much easier and faster than copy + paste right :)

Thursday, February 5, 2009

Before you print your excel sheet

I got this question from a session I recently conducted, about a few things that can be done to your excel sheet just before you click on that 'Print' button.

My excel sheet here is a lengthy one, horizontally.

When I press on the 'Ctrl + End' buttons to show the last cell, see how far my data is stretched across.

Before anything, allow me to make my data look much more interesting, here I have formatted them into a table, and chosen a girly pink theme to it :)

My columns are consuming too much space, let me do 'Auto-fit' quickly. Select the whole table by just clicking on the tiny diagonal arrow at the top left corner, then double-click on any one of the columns' borders.

To have a look at how the document will look like after printing, we could go to Print Preview or from the View tab, click on Page Layout for the whole overall view of the pages.

My table's first row of columns contain the year, and I want this to be repeated on all the printed page, but first so that it's easier for you to notice, I have right-clicked and chosen Format Cells to put in a purple fill into the cells.

Here is how it is now from the Page Layout View.

Right now, click on the Page Layout tab, and the Page Setup group label, click on Print Titles. In the 'Print Titles' category of the dialog box, there is a portion for 'Columns to repeat at the left:', click into the text box there and select the left-most column of the table; '$A:$A' will be the value in the text box.

Now see the outcome from Page Layout View.

Sometimes you may want to adjust the Page Breaks in the excel sheet so that you can determine/control what is to be printed in one page, well, let's click on the Office Button and choose Excel Options. In the Advanced tab, check this capability:

Having done so, click on the View tab, and choose Page Break Preview.

You may now click on the intended Page Break and drag it to your point of choice. Another way to insert a Page Break would be to select the column and right-click, and select 'Insert Page Break'.

The conventional way of doing the Page Break is from the Page Layout tab, in the Page Setup group, there is button for Breaks, click on it and you will see options to 'Insert', 'Remove' or to 'Reset' page breaks.

Afterthis, please remember the environment when you do you printing, that means, make sure you have done all your editing and is pleased with the Print Preview or the Page Layout View before you decide to go ahead with the printer.