Excel and the Web

Typically we would use Excel for our data, records, and also calculations, and right now I have an excel sheet which requires me to submit my Total Sales in Ringgit (RM) and also in USD. Before anything, let's format the cells into Currency with 2 decimal places, how I did that was just by highlighting the two cells and right-click followed by the option 'Format Cells'. In the Number tab, make the proper formatting.

I went on the internet and browsed for a site for me to refer the currency exchange rates to convert my RM to USD, and I found one on MSN. Now I am going to copy the URL of the site.

Returning to my excel worksheet from the browser, I will now refer to that currency exchange site in my RM to USD conversion formula. In excel, go to the Data tab, and in the Get External Data group, click on the feature called 'From Web' and this will actually launch a browser-like window from within excel. Paste the copied URL into the Address box in the excel browser and 'Go'.

The currency exchange website will now be loaded into the excel browser, the only difference between this and what we saw in the internet browser is that here there are tiny yellow boxes with arrows in them for us to specify the areas that we want to import into our excel sheet.

I'm going to select this Currency Rates table here by clicking on the yellow box and then hit the 'Import' button at the bottom.

After this, just specify the area to place imported data, for this I am going to select the current sheet, just below my Total Sales value. You can choose your own settings for the imported data, to do this click on the Properties button.

You will see a window like this where you can select the suitable 'Refresh Control' (for instance) for that data that you're importing from the web. Everytime the data changes in the browser, it will be reflected in your excel sheet.



Click 'Ok' and you will have the same currency rates table placed in your excel sheet at the location that you have specified. Here I have right-clicked and when you choose the 'Data Range Properties' you will see the same settings when you hit the 'Properties' button previously. 'Refresh' there indicates that the data is live/linked to the web.

I shall refer to the value here and use it in my conversion formula and not have worries of inaccuracy or doing repeatitive formula updates.

Oh yea.... CHEERS to an exciting 2010 for all.... especially with the launch of Office 2010 coming soon :)

Headers, Footers, Page Numbers

I have been so caught up with work and my weekends have been too short so yea...forgive me for leaving such a big gap since my last post.. Now let's get back to more 'work smart' with Office 2007, shall we ;)

This post comes from a question which I received from a user when he was creating his documentation. This was the scenario : his document has a cover page, a table of content, and the document body. He had problems fixing the header and the page numbering.

Let's start on this, what I have done in this document here is to put in all my content, the proper headings, generated the table of content and also inserted the cover page (notice the lovely sunflower there :) ) First step would be to settle the content of your document.

Right now, I have just inserted the header (Insert tab > Header > choose the header which you want), the headers have come out on all 4 pages.

I also want the page number to be shown in the header, so I've set my cursor to the end of the header (after '2009') and since the cursor is within the the header, the contextual tab for Header & Footer Tools > Design tab is shown in the ribbon. Choose the Page Number > Current Position and select the number formatting of your choice.

The header now contains the document name, year and also the page number. Next I would like to set a new section for the 'Introduction' portion onwards. Therefore my cover page and table of contents (toc) will be in Section 1, and Introduction onwards will be in Section 2. Click at the beginning of the heading 'Introduction' and go to the Page Layout tab > Breaks > Sections Breaks (Next Page) to do this, or refer to my previous post. I only want my content ('Introduction' onwards) to have the header and page numbering, that starts from page 1.

Click within the 'Header - Section 1' and in the contextual tab for Header & Footer Tools > Design tab in the Options group, make sure the 'Different First Page' option has been cleared, afterthat navigate to the next section by clicking on 'Next Section'.

We are now in Header - Section 2, perform the same checking, make sure the 'Different First Page' option is cleared as well, and turn of 'Link to Previous' so that Section is independent of Section 1.

Once we have done those two checks, click within the document body of Section 1 (cover page and toc), go to the Insert tab, and in Header, choose 'Remove Header' right at the bottom of the options list. This will remove the header in Section 1.

Move to Section 2, choose Page Number (still in the Insert tab) and click on 'Format Page Numbers'. In the Page Number format window, under 'Page Numbering' select the option to Start at: "1" and voila...

That's all there is to it, of course if you follow the steps accordingly.. ;) These steps apply for footers as well.

Customizing a Multilevel List

If you get frustrated with multilevel lists and customizing them, then you are on the right page. Fret not, let me take you step by step to doing this, and hopefully after this you would wonder, 'why was there any frustration in the first place again?' :)

As usual, I have a dummy document ready here which the headings have been formatted into desired styles, these screenshots will give you a run-through on the diffrent headings which have been set.

Let's have a better look via the Document Map.


In the Home tab, within the Paragraph group, you will find 'Multilevel List', and here there are already a few preformatted templates for you to choose from.

I want to have control on how my multilevel list is formatted, and need to do my own customization based on the format which has been fixed for me to follow (for instance).

First step, click on 'Define New Multilevel List' and when the window appears, click on the 'More' button at the bottom to have a look at more options.

Here's the setting for the first level, I have highlighted in yellow the areas which I have changed. I want the first level to be linked to Heading 1, and for this customized multilevel list to show up to 4 levels in the gallery.

For the second level, I want to inherit the number from Level 1 followed by ".1" and so on. To do this, clear the formatting for number text box first, then choose 'Level 1' from the dropdown of 'Include level number from'.

This is what you will see in the 'Formatting for number' text box.


When you have done that, then choose the formatting for after the '.' (point) symbol.

Here is a clearer screenshot to show the changes in the settings that I have made for Level 2 in the multilevel list.

Settings for Level 3, notice for all the changes that is specified, it is previewed on the top left corner so that changes especially in the level and text alignment can be done correctly.

Lastly, level number 4 settings are as follows:

Click on the Ok button and the customized multilvel list is applied to your document.

Here I have typed "(i)" for a point which I have inserted in the document, and when I press the , the formatting for Level 4 automatically kicks in.

When the multilevel list is working as you expected, just right-click and 'Save in List Library". Easy peasy :)

Bubbles in Excel chart

How you ever created a bubble chart in Excel? I reckon that most of us stick to the usual bar and pie chart and the occasional line chart. Right now, I'm going to show you how to create a 3D bubble chart to represent your data.

The information in my excel here is about the market share of numerous browsers for March 2009 which I obtained from Market Share site.

First of all, let me format the data block into a table to ease the process of adding columns, rows and formula.

I have just added another column for the existence duration of each browser respectively. Formula used is 2009 - (Start Year), simple right.

I have moved the columns around a bit so that I can get the exact visualization for my bubble chart. Ok, the most obvious difference of the bubble chart compared to the rest is that it needs 3 parameters - x, y, and z value which need to have numerical value. Right now, x-axis is the Start Year (Release Year) of the browser, y-axis is the percentage of Market Share (March 2009) for the browser and z value would be represented by the size of the bubble which in my chart reflects the duration or how long the browser has been in the market. So that's regarding understanding and positioning your data.

Let's start creating the chart, select the data for column 2 till column 4 (Start Year, Total Market Share, Duration), then click on the Insert tab, and in the Charts group, choose 'Other Charts', in the drop down, you will see 'Bubble with a 3-D effect', alternatively you could use the Office 2007 shortcut keys - simply type Alt (to see the shourtcut keys) - N (Insert tab) - O (Other Charts)


There you go, your very own Bubble chart.

My work's not done yet, for I'm not too satisfied with how it looks. I want each bubble to represent each browser and have it's own significant colour. When the chart is generated automatically (remember earlier I mentioned that x,y,and z has to have numerical value) very minimal 'personalization' so to speak was done to the values.

Let's make our chart look better, look at the ribbon, the contextual tab for Chart Tools has appeared and in the Design tab, click on Select Data.


In the dialog box, what we need to do is to edit the Series.

Choose the appropriate values for the Series Name, x, y and z value.

So for the first bubble, the values should look like this, I have highlighted the data row for the bubble.

Next, 'Add' the Series and do the same thing for all the browsers' information.

Finally, the 'Select Data' dialog box would have all the browser names enlisted in the Legend Entries area.

Do you like what you see... I know I do :)

Master Slides

I have a useful tip for PowerPoint, so here's the scenario, you have a deck of slides to build, and you need to customize them with logo and such but you want to save time and avoid doing repeatitive copy+paste on the individual slides (especially for those logo which are to be placed in your slides). Here is my deck which is on Earth Hour 2009:

In order to begin customization work, click on the View tab, and select 'Slide Master' from the Presentation Views group.

This is how the Slide Master view looks like, all the different slide layouts are lined up on the left.

Like I mentioned earlier, I need to insert the logo into the slide. I do that by selecting on the layout (Title Slide Layout) in which I want this logo to be placed, click on the Insert Picture from the Insert tab. The logo which I will now insert is the WWF panda logo and after this the EarthHour logo.

Move these logos to the desired locations. I want these logos in the Title and Content Layout as well, so I have repeated these steps for that layout.

Hmm..I'm not too satisfied with the background gradient, let me do some adjustments first - Slide Master tab, Format Background and enter the changes in the dialog box.

You can also Insert Layout and Insert Placeholder from the Slide Master tab.

I have chosen to Rename my new layout with a picture placeholder 'Photo Layout'.


Now Close Master View (still in the Slide Master tab, far right) to return to the normal slide layout. I have inserted a new slide, and when I right-click to change the layout of the slide, I see the 'Photo Layout' there which I just created. Notice that my logos have appeared in my other slides too according to the layout (Title Slide Layout and Title and Content Layout) that was selected.

To insert a picture into the placeholder, I simply click on the icon and select the picture of my choice.

Don't forget to save the theme so that you can reuse it in future. Go to Design tab, click on the dropdown for Themes, and you will find the option to Save Current Theme where you will be prompted to rename the theme, please do not change the default path or location for theme to be saved in.

I feel Slide Master is important to save all that time we spend on copy + paste and rearranging elements in our slides, give this a try :)