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 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!

Shapes versus placeholders: the crucial differences

In the view Slide Master of PowerPoint, you can insert both text boxes as text placeholders. Both seem the same at first sight, but when you return to the normal view of your presentation differences become cleary noticeable. This post highlights the differences between text boxes and text placeholders or, in a wider context, between shapes and placeholders.

Even if you’ve never heard of Placeholders before, you are probably more familiar with them then you might think. Take the first slide of a new presentation for example. It contains two frames in which you can enter text according to the instructions, but which you also can delete, resize, colour,…. These flexible frames look like text boxes but are in fact Text Placeholders.

If you go to the Slide Master and scroll through the different layouts you’ll notice that there are -in addition to frames for text- also frames for pictures, charts, tables, SmartArt, Media, Online pictures or content in general. These are all placeholders.
If you make your own template, you can insert these placeholders by the feature Insert Placeholder on the tab Slide Master.

Besides placeholders, you can also insert shapes (including text boxes) in the Slide Master view. But unlike the first, shapes cannot be adjusted in the normal view. If you insert for example a blue text box in the Master view and return to the view Normal, you will notice that this is a fixed element on the slide which is impossible to select, and thus impossible to adjust.

There is also a second difference between placeholders and shapes: a placeholder can be put on the back or foreground on the slide whereas a shape will always remain in the background when you insert them into the slide master.

However, both shapes and placeholders are useful when it comes down to making a PowerPoint template. How to use these two elements correctly is for a future post.

Based on PowerPoint 2016.

The mystery of the option ‘Resize shape to fit text’

There are a few issues with the PowerPoint template of Howest that both users and I would like to see resolved. Now they cause confusion and lead to an inconsistent Howest look (and most likely we are not the only one with this issue).

Difficulties arise in two specific situations: when users switch the layout of a slide or use the feature ‘Reset’ in normal view. In both situations pink placeholders with the Slide Master settings ‘resize shape to fit text’ are involved.

The Slide master slides contain instructions for the user.

The first time you enter text in such pink bar (in normal view) the size of the bar corresponds to the length of the text. Settings go into effect as you would expect.

But when you use the feature Reset the pink bars take over the bar size of the Slide Master. Although the setting ‘resize shape to fit text’ is checked, the size of the bars do not longer correspond to the length of the text.

The feature Reset “resets the position, size, and formatting of the slide placeholders to their default settings”. This is taken very literally if you click on Reset.

The same happens when you change the layout of a slide with a pink banner via the feature Layout on the tab Home.

To get this right, you briefly need to check another option (for example ‘Shrink text on overflow’) before checking the option ‘resize shape to fit text’ again.

No wonder that this workaround confuses users…

Based on MS Office 2016