Part 3: PlanMaker
A completely new world: tables (in tables)
First of all, the rather unfortunate term “tables” for the feature described below was not invented by us, but by the manufacturer of another well-known spreadsheet. To avoid creating even more confusion, we have decided to adopt the term for PlanMaker.
What’s this about? In simple terms, these tables are areas within worksheets which can be formatted with a single click, to which you can add data dynamically, and which can be referenced using a special syntax. How this works and how you can benefit from it is explained below.
Defining an area as a table
Defining an area as a table is quite simple. To do this, you only have to select one of the cells that lie within the range you want to convert into a table and then click Insert | Table. In the classic menus, this command can be found under Worksheet > New Table.
PlanMaker then uses the following dialog box to determine which area should be defined as a table:
In the screenshot above, look at the table behind the dialog box: Although only a single cell was selected, PlanMaker automatically recognized the source area for the table and suggested it. You can simply accept the suggestion or correct it first.
If you are satisfied with the selected area, confirm by clicking “Insert.” You’ll then see by the appearance of the area that it has now been formatted as a table:
Now, what happened? In addition to the automatic formatting with a table style—which you can recognize by the blue and white banded rows in the figure above—this area now offers the following options:
Automatic AutoFilter: The first line of the newly created table now contains an AutoFilter. This works in the same way as an “ordinary” AutoFilter in the worksheet, but filters only those values that are inside the table. It does not affect the cells below the table. As an added bonus, you are no longer limited to one AutoFilter per worksheet, since every table now has one of its own.
Dynamic referencing: The contents of tables can be referenced using a special syntax that makes it much easier to add data dynamically.
For example: Let’s say you want to calculate the average of the column “2016” in the table shown above. You can easily do this by entering “=AVERAGE(Table1)” in any cell.
At this point you might wonder what advantage this syntax has over the entry “=AVERAGE(D2: D11),” which would give you the same result. This is where the dynamics mentioned above come into play. If additional values are added to the table, they are automatically included in the calculation:
In this way, you can be sure that the average of the entire column “2016” of the table is always calculated, regardless of how many cells the column contains.
How to add new values to a table? That's quite simple, too – you have three different options:
- You can either select the bottom right cell of the table and hit the Tab key,
- or you can enlarge the table area by dragging the little triangle on the bottom right corner of the table,
- or you just enter a value in a cell located directly to the right or below the table.
If you prefer working with dialogs, you can also extend the table via Table | Range (with classic menus: Worksheet > Table > Table Range).
Dynamic cell references are also extremely useful for tables that act as a source for pivot tables. If new values are added to the source table, they are also included in the pivot table after selecting “Update pivot tables.”
The new syntax for addressing table contents offers you numerous other options, such as addressing several columns of a table, addressing certain rows, and so on. However, listing all these possibilities here would go beyond the scope of this article. Those interested can find a detailed description in the “Tables in tables” section of our PlanMaker manual.
Special formatting options: As you’ve already seen, a table style was automatically applied to the area marked as a table. But this is not the only style you can apply to your table. Numerous other styles are available in the context-sensitive ribbon “Table.” To activate the corresponding ribbon tab, select any cell of your table; then the catalog of further table templates appears under Table | Table styles:
If you are using the classic user interface, you can find this template catalog under Worksheet > Table > Table settings in the “Style” tab.
As you see in the screenshot above, the tables offer you some other formatting options besides the templates, such as highlighting the first or last column or the automatic variegated “bands” of the rows or columns.
The options Header row and Total row represent special features: besides formatting options, both elements also offer special technical features:
The header row provides the controls for the AutoFilters mentioned above. You can filter and sort the contents of the table using the little arrows on the right of each cell:
The total row contains control elements in each cell as well. However, these are used to calculate key figures from the content above. For example, the average of the data above can be calculated in the total row with a single click:
Incidentally, you can also change the contents and formatting of tables using conventional methods. For example, if you want to highlight a cell, you can easily change its font color. You can also use the usual formula syntax within a table.
New cell styles
You might know how to work with cell styles from older versions of PlanMaker. Thanks to numerous new styles that you can apply from an attractive catalog, this is easier than ever before—and you'll always be compatible with Microsoft Excel 2016. You’ll find the catalog under Home | Cell styles:
When using the classic interface with menus and toolbars, you’ll find these styles in a classic drop-down menu on the Format toolbar.
Set the size of the tabs in the worksheet tab
In response to many requests from customers, we have added a handy little feature that makes working with multiple worksheets more convenient: the size of the tabs in the worksheet register can now be set by the user. If the tabs are too small for you, you can enlarge them in the settings in the “View” tab:
The worksheet tabs then appear in the specified size:
Font sizes between 4 and 48 are permitted.
We hope to have provided you with a sneak peek at some of the most important new features of PlanMaker 2018.
Thank you for your comments on our last blog post. Please feel free to share your impressions and experiences with us once again.
Have fun trying out the new SoftMaker Office!
The free 30-day trial version is available here.