How to format your Excelsheet and improve your productivity

These days it’s all about christmas lights and glühwein, but for many it’s also the end of the fiscal year. And sadly, that means that we are faced with many figures, calculations and formulas. Besides the glühwein this complex data might make your head spin, especially when the info is poorly presented. Time to clear your mind and your doc!

In this article I’ll explain how to display data in an Excel worksheet and which formatting can be set automatically.

The first step to a clear and professional looking worksheet starts, like always, with the theme. Change the theme of the default template into the customized one (that you’ve made) of your organisation (to do so: go to tab Page Layout > dropdown menu Themes > your theme). The corporate fonts and colours are now integrated in the file and also, content like shapes, tables, charts,… that you have inserted or will insert will be based on that specific theme.

Further formatting depends on the kind of information you would like to format:

How to format text into a worksheet
Don’t start each line of text at the beginning of a new row. It is frustrating to do and confusing for co-workers who want to edit the text but don’t know which cell they have to adjust.

each line of text starts at column A: A4, A5 and A6. The cells next to A4, A5 and A6 are empty.

Don’t resize a cell either to insert all text. That one big cell will be a pain to format the rest of your content. Instead, make it easy on yourself and enter (a) paragraph(s) of text in a text box. Also, in this way you have more functionality at your disposal (text and paragraph editing tools)

If you don’t like the look of the text box of which the outlines do not cover the cells perfectly, you can uncheck the Gridlines on the tab View. But it’s better to do this after you’ve done with formatting, cause they are quite handy when it comes to Ranges of cells and Tables.

How to format typical Excel content like a cell, a range of cells, tables and charts

Cells
The most popular commands are displayed on the tab Home.  For more options, right click on the cell and choose ‘Format Cells…’, you can format Number, Alignment, Font, Border, Fill and lock or hide cells.

But it would take a long time to format each cell manually. For different sorts of content you can use cell styles or set a New Cell Style… (tab Home). If you use these cell styles consistently you’ll obtain a well structured worksheet that enables you to identify the following information at first glance: Good, Bad and Neutral / Data and Model / Titles and Headings /… there are also Themed Cell Styles and each style can be modified.

Tables

You can quickly change the look of a table via Table Styles on the contextual tab Table Tools. These are based on the theme of the document, but if these don’t fulfil your needs, you can also create a New Table Style. Click on the dropdown menu Table Styles > New Table Style… Unlike Styles in Word, it is not possible to copy the direct formatting of a table straight away to create a new table style. You can set the New Table Style as default table style for the document.

A Range (of cells)
As you can format a table quickly via Table Styles, it is clever to convert your range of cells into a table, give it the right formatting, i.e. table style and convert it back again to a Range. The formatting of the table will be maintained. This workaround might save you quite some time!
To convert a range into a table: select the cells, click on the tab Home > Format as Table or on the tab Insert > Table. To convert it back again to a range, click on Convert to Range on the contextual tab Table Tools.

Tip: Unlike the cells of a table, you can merge cells of a range (tab Home > dropdown menu Merge & Center) If you want to merge cells of a range, first give it the right formatting (by converting it into a table, choose a table style and convert it back again to a range, see above) If you convert a range with merged cells into a table you’re asking for trouble!

Header 1	Header 2	Header 3
Cell	Cell	Cell
Merged cell		Cell
Cell	Cell	Cell

Range with a merged cell

Header 1	Header 2	Header 3
Cell	Cell	Cell
Merged cells		Cell
Cell	Cell	Cell

After converting the range with the merged cell into a table: the merged cell is unmerged.

Header 1	Header 2	Header 3
Cell	Cell	Cell
Merged cells		Cell
Cell	Cell	Cell

After converting the table into a range again: the formatting of the table is preserved, but the merged cell stays unmerged.

Tip: If you want to clear the formatting of a Table or a Range, click on the dropdown menu Clear on the tab Home and choose Clear Formats.

Charts
The same for tables goes for charts: they are based on the theme of the document, but you can also adjust a chart and save it as a template.

Before you unpack your christmas gifts don’t forget to pack the workbook as an Excel template: go to the tab File > Save As > Save as type: Excel Template (.xtlx). Now you have a template which you can reuse year after year, you just have to open it as a normal Excel Workbook (.xlsx) and replace the content.

If you want to print the workbook, take a look at the Page Break Preview on the tab View to see how your file will be printed.

Happy holidays!

How to create a Christmas card in PowerPoint

You don’t necessarily need Adobe Photoshop to surprise your colleagues with a thoughtful Christmas card. With your MS Office skills, a little creativity and some basic knowledge of graphic design you can achieve quite a good result.
This post guides you through the process of creating a Christmas card that breathes the look and feel of your organisation, starting from a blank slide in PowerPoint…

Step 1: resize the slide to the format of a greeting card.
If you are planning to send it the old fashion way (which I personally love) do take the format of your envelop into account. A standard greeting card 100 x 150 mm fits a default envelop 114 x 162.

Go to the tab Design > dropdown menu Slide Size > Custom Slide Size… choose the proper width and height and decide on the orientation of your card: portrait or landscape.

Step 2: create your card
The logo of your organisation will help you to get started. It is your biggest inspiration to design your card. What are the colours, fonts and shapes of the logo? If you integrate the style elements and the general style of the logo in the rest of your card, the whole will look a lot more coherent. This is another example when a customized MS Office theme comes in handy.

Insert the logo(s) (in Master View) and position them as described in the corporate style guide of your organisation. Give each logo plenty of room to breathe! Go to the tab Design and choose the theme of your organisation. Work with a layout which has no placeholders.

I’ll get to work with the logo of Howest Industrial Design Center.

Now think of Christmas and everything around it: stars, snow, christmas trees and sweet wishes,… but don’t get too excited about decorating your card: Less is more! So keep your design simple.

The following video is an example of how a christmas card in PowerPoint can be made. I’ve found the picture of the cardboard paper on the background on www.freestock.ca

The video illustrates how to align, rotate, flip horizontal, group, make text transparant… these are a few graphic possibilities in MS Office, but there is much more!  You can make your own (background) pattern for example, as the following video illustrates.

Why not insert a picture in a shape that is related to christmas, a star for example. In the post How to get your favourite image in a shape I’ve explained how to do this.

 

 

 

 

 

 

 

The feature Ink Annotations (only in PowerPoint 2016) makes it possible to add freehand pen and hightlighter strokes to your document. You can find it on the tab Review, Start Inking.

 I’ve used it to create snowflakes and some snow on the roof of Industrial Design Center.

Step 3: save the slide as a jpeg.
Once you’re done, save you slide as a jpeg. Unfortunately for some reason the quality of the image is not what it should be. You can fix this by following the instructions in this article by Motifolio.

Step 4: Print and/or share your Christmas card !

I hope you can use  some of the techniques I’ve illustrated to create one yourself. If you have an idea and don’t know if it’s possible in MS Office, let me know and we can figure it out.

And don’t hestitate to share your Christmas cards!

 

 

 

 

 

 

 

 

Merry Xmas!

 

How to convert an old presentation into a corporate template

The introduction of an unique Howest template raises the question of how the staff of Howest can apply this template to an existing presentation. How smooth the conversion goes depends on a few crucial factors, just like it did with the conversion of the slide size in my previous post. And also in this story, the pre-set layouts of the Slide master are playing a major role. This proves again how important they are in terms of creating a presentation.

The slide sorter gives an overview of all the possible slide layouts of the Howest template with aspect ratio 4:3

Open the template (like the 3:4 template of Howest in the picture above) that you like to use. Import the slides from an old presentation into this template via the tab Home > drop-down menu of the command New slide > the option Reuse Slides…

On the right, the window Reuse Slides appears. Locate the slides that you want to insert, either from a slide library or from a PowerPoint file.

Make sure the option Keep source formatting (below the window) is ticked off before you right click on one of the slide thumbnails in the right window and select Insert All Slides.

The result after inserting all slides into the template, not quite what I had in mind…

How your presentation looks like now and in which degree the result corresponds to the layouts of the template all depends on how your old presentation was created.

If in the old presentation no layouts (or associated placeholders) of the Master View were used, you will have to transfer the content of each slide manually to a proper layout of the template.

But if your slides in Normal view are based on the layouts of the Slide master, the conversion will go more automatic. Though, you’ll still need to verify each slide and you will probably need to make quite a few minor adjustments or choose a better layout for a slide.

To change the layout of an imported slide: Click right on a slide and choose Layout. The selected layout is the current one. You change this just by selecting another one (of the desired template).

The last 5 layouts don’t belong to the original template.

Besides being patient, the best thing to do is to compare each slide in both presentations (the old one with the new one) via the Tab View > Arrange All.

Again, if you have a better workaround, feel free to share!

How to resize your presentation

As promised, a sequel to the article of last week about the slide aspect ratio 4:3 and 16:9.

Most likely you will convert 4:3 format into 16:9 rather than vice versa, as widescreen has become the new standard (the 4:3 screen format of iPad is an exception) and the same goes for slides.  Anyway, this blog post covers the conversion in both directions. In the article I assume that the presentation you would like to resize, is created in a template and pictures and text are inserted in placeholders.

Is this a story with a happy ending?  Well, it depends on a few factors…

There are different methods of resizing your presentation. One option is to resize your presentation via the tab Design, Slide Size. Another way is to copy all the slides from the original presentation and paste them into a new presentation which has the desired aspect ratio (Choose past option Keep Source Formatting)
Which path you take, does not really matter. Just make sure you work with a copy of the original presentation, because the original might come in handy in the future!

        

    
After converting a widescreen into a 4:3 format, the photo looks distorted.

At this point all the graphics or logos in your slides will be out of proportion (Scale Height and Scale Width will be dissimilar). They will look either stretched or distorted. To fix this, select all the slides (to do so: select the first slide, hold the shift key and select the last slide) and click on Reset on the tab Home, this command resets the position, size and formatting of the slide placeholders to their default settings. So thanks to the feature Reset pictures get their normal proportions back. But you are not out of the woods yet, because there is also a drawback to this feature.

Using the pre-set layouts of the Master View is one thing concerning formatting slides, but not the only one. Often, there has been also a lot of “direct formatting” in Normal view. A few examples to clarify what I mean: pictures you’ve positioned via the crop tool in the placeholder, font colour you’ve changed, words you’ve made bold, new layouts you’ve created because the layout you are looking for was not foreseen in the template,… The Reset feature takes no account of these kind of adjustments in Normal View. They just get lost after clicking Reset. 

    
After converting a widescreen into a 4:3 format and clicking on Reset, the photo looks okay, must be repositioned in the placeholder again.

If you know for sure you haven’t done a lot of this “direct formatting”, the story ends well for you (checking the font size and indentation is the only thing left to do, because they might have changed after conversion).

But it can be quite frustrating if you’ve made a lot of these little changes in the Normal view and time consuming to restore everything to the way it was before… As an alternative you can leave the Reset feature for what it is and Crop > Fill each picture via the contextual tab Picture tools which appear after selecting a picture.

Converting slides often takes a long time. If you know a  more efficient way or a magical button to convert slides without losing any direct formatting AND with respect to the aspect ratio of the pictures, please share and make my day!