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!

Get rid of boring Word docs!

In the beautiful brochures – which are made by the communication department of Howest in the Adobe suite – text is sometimes surrounded by coloured frames. It is one of the ingredients for a fresh Howest look.

Luckily, this types of style elements are not exclusive to the Adobe suite. You can perfectly integrate them in MS Word to spice up long, dull documents. Though the method of working in Word may differ from the one in Adobe and is not always so obvious.

Take the coloured frames for example, you may think – just as I thought – to integrate them via the feature Text Box on the tab Insert. This is okay for normal text, but I noticed that text boxes do not work for numbering like in multilevel styles and footnotes. If you put such “special” text in a text box, the numbering of the list is no longer correct.

A solution to integrate this element without losing the numbering? Insert a
Rectangle (tab Insert > shapes) instead of a Text Box, give it a lively colour and put it behind the text via the feature Send Backward > Send Behind Text on the contextual tab Drawing Tools.

The document above is made in Word. Besides little style elements like the coloured frames, the page number in little triangles, the text balloons, … the document catch the eye by using a lot of colour and contrast!

By integrating these little style elements you are one step closer to a fun and readable document!

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.

How to make coloured headings: PowerPoint versus Word

One of the most striking elements of the corporate identity of Howest University College West Flanders are the pink (magenta) text bars, as you can see in the folder below.

The layout of this folder is made by the communication department in the Adobe suite. But what about MS Office? In other words, how can lecturers of Howest implement this style element in a presentation or in their course material? I tried it out in PowerPoint and Word!

This post seems very much focussed on the branding of Howest, but similar headings are very popular in the world of graphic design. I collected a few examples on Pinterest.

In Word this style element can be introduced as a heading that you can add to the styles gallery if desired. Select the text of your heading and click on Shading on the tab Home. Choose the colour of the shading. (For your convenience you can install the corporate colours as theme colours, so you don’t have to set the RGB values of your corporate colours each and every time.)

Depending on whether you only select the text or the entire paragraph, this is the result:

HERE ONLY TEXT IS SELECTED

HERE THE ENTIRE PARAGRAPH IS SELECTED

In PowerPoint this style element can be used as the title of the presentation, to emphasize the central idea of each slide, to present a quote,… But unlike Word the feature Shading is not available for text in PowerPoint (if I am mistaken please correct me!) Also different from Word is that text in PowerPoint is always inserted in a text box or a placeholder (slide master)… So the way to achieve the same look is by colouring this boxes with the function Shape fill on the tab Home or the contextual tab ‘Drawing tools’. Resize shape to fit text ensures that the pink box corresponds to the length of the text (to do so: right click of the mouse on shape, format shape, size and properties, text box).

This is the result in PowerPoint:

If you take a look closer and compare the end result of the two methods, you can notice a tiny difference between the pink bars in Word and the ones in PowerPoint: the headings in Word look more cut off than the text boxes or placeholders in PowerPoint. This is because text boxes or placeholders have a left, right, top and bottom margin of which you can adjust the spacing. This does not seem possible via the feature Shading in Word.

    

Of course, it is also possible to introduce the pink bars as a text box with margins in Word, but since Word is a word processing program it is more logical to implement this style element as a heading style in the Styles gallery.

Based on MS Office 2013