Tabulys guide

Data

The step-by-step guide to every command in the Data menu of Tabulys.

Sort & Filter

Where to find it
  • Data › Sort and Filter
  • Toolbar

Puts your rows in order, or hides the ones you are not interested in. Both work on the range you have selected, so take in the header row.

  1. Click a cell inside the data, taking in the header row.
  2. Click Sort & Filter: the button opens a menu, it does not sort on its own; the arrow that appears on a column letter opens the same menu, after moving the active cell into that column.
  3. Choose Sort A to Z or Sort Z to A to order the rows by the column you are in, or Custom Sort… to order on several columns at once.
  4. Choose Filter to put the ▾ arrows on the header cells, then narrow the rows from an arrow.
  5. Use Clear to drop the criteria and show every row again, and Reapply after you change the data.
  6. Entries that cannot do anything are greyed out: Clear stays unavailable until a filter is really applied.

Tip Select the whole table, never a single column: ordering one column on its own pulls it out of step with the rows beside it.

Sort A to Z

Where to find it
  • Data › Sort and Filter

Reorders the rows by one column, smallest value first: A before Z, earliest date at the top. It is the one-click sort, with nothing to set.

  1. Click any cell in the column you want to sort by.
  2. Click Sort A to Z.
  3. Check that each whole row moved with its value, not just the one column.

Tip To order by more than one column, use Sort instead.

Sort Z to A

Where to find it
  • Data › Sort and Filter

The mirror of Sort A to Z: it reorders the rows by one column from the largest value down. Use it to bring the biggest numbers, or the most recent dates, to the top.

  1. Click any cell in the column you want to sort by.
  2. Click Sort Z to A.
  3. Check that each whole row moved with its value, not just the one column.

Tip A sort rewrites the order of the rows for good; undo it straight away if the result is not what you expected.

Sort

Where to find it
  • Data › Sort and Filter

Sorts on several columns at once, so rows that tie on the first column are ordered by the second. Use it when a single-column sort still leaves the rows you care about jumbled.

  1. Select the range you want to sort, or click a cell inside it.
  2. Click Sort.
  3. In the dialog that opens, choose the first column to sort by and its direction.
  4. Add the further columns, in the order they should be applied.
  5. Confirm to reorder the rows.

Tip The order of the columns in the dialog is the order they take effect in: the first one decides, the rest only break ties.

Filter

Where to find it
  • Data › Sort and Filter
Ctrl+Shift+L

Hides the rows that do not meet a condition and leaves the rest in place. Nothing is deleted — the hidden rows are still there, out of sight.

  1. Click a cell inside the range you want to filter.
  2. Click Filter.
  3. Use the filter control that appears on the column heading to set the condition.
  4. Read the rows that remain.

Tip Filtering only hides rows; Clear brings them all back.

Clear

Where to find it
  • Data › Sort and Filter

Removes the filters you applied and brings every hidden row back into view. Use it when you have finished looking at a subset and want the whole range again.

  1. Click a cell inside the filtered range.
  2. Click Clear.
  3. Check that the rows which were hidden are showing again.

Tip Clear undoes the filtering only; rows that a sort moved stay where the sort put them.

Reapply

Where to find it
  • Data › Sort and Filter

Runs the filter you already set over the data as it stands now. Use it after editing or adding rows, so newly matching rows appear and rows that no longer match drop out.

  1. Edit or add rows in the filtered range.
  2. Click a cell inside that range.
  3. Click Reapply.
  4. Check the rows now showing against the condition you set.

Tip Reapply keeps the condition you already chose; to change the condition itself, use the filter control on the column heading.

Advanced

Where to find it
  • Data › Sort and Filter

Filters using conditions you have written into cells rather than picked from a list. Use it for conditions too involved to express in the column filter.

  1. Write the conditions into a separate range of cells.
  2. Click a cell inside the data you want to filter.
  3. Click Advanced.
  4. In the dialog that opens, point at the data range and at the range holding the conditions.
  5. Confirm to apply the filter.

Tip Keep the criteria cells clear of the data, so filtering never hides the conditions themselves.

Text to Columns

Where to find it
  • Data › Data Tools

Breaks the contents of one column into several. Use it when a whole record — a full name, an address, a date — arrived packed into a single cell.

  1. Select the column of cells you want to split.
  2. Click Text to Columns.
  3. In the dialog that opens, set how the text should be divided.
  4. Confirm to write the pieces into the columns to the right.

Tip The split writes over the columns to the right of the selection, so leave enough empty ones there first. If the selection spans more than one column or holds no text, nothing is split: a window says what is wrong and how to fix the selection.

Flash Fill

Where to find it
  • Data › Data Tools
Ctrl+E

Looks at the example you typed beside your data and fills the rest of the column the same way. Use it for pulling values apart or joining them when a formula would be more trouble than it is worth.

  1. In the column beside your data, type the result you want for the first row.
  2. Click the cell below the example.
  3. Click Flash Fill.
  4. Check the filled column and correct any row it read wrongly.

Tip Flash Fill writes values once by following your example, so a later change to the source data does not carry through. If no example or no rule can be found, nothing is filled: a window says why and what to type so the command can work.

Remove Duplicates

Where to find it
  • Data › Data Tools
  • Pane › Table

Deletes rows whose values repeat a row already in the range, leaving one copy of each. Use it to clean a list assembled from several sources.

  1. Select the range you want to clean, or click a cell inside it.
  2. Click Remove Duplicates.
  3. In the dialog that opens, choose which columns must match for two rows to count as the same.
  4. Confirm to delete the repeats.

Tip This deletes rows rather than hiding them: check the result, and undo at once if more went than you meant.

Remove Duplicates

Where to find it
  • Data › Data Tools
  • Pane › Table

Deletes rows that repeat the same values, leaving one of each. It is the first thing to run after pasting several lists together.

  1. Click any cell inside the table.
  2. Click Duplicates.
  3. A dialog opens where you set the options.
  4. Confirm to delete the rows that repeat.

Tip This takes rows out of the table, so check the result and undo straight away if it removed more than you meant.

Data Validation

Where to find it
  • Data › Data Tools

Sets a rule for what the selected cells will accept, so a wrong entry is refused as it is typed. Use it on the cells other people are going to fill in. If the chosen cells are part of an existing validation, the new rule covers only them and the other cells keep theirs; relative references are read from the active cell, as in Excel.

  1. Select the cells the rule should cover.
  2. Click Data Validation.
  3. In the dialog that opens, set the rule for what may be entered.
  4. Confirm, then type a value that breaks the rule to check it is refused.

Tip In the menu, Data Validation opens a submenu with the related commands. A list can take its values from another sheet: with Allow set to List, type =List!$A$1:$A$3 in Value / Source, or point at the cells with the RefEdit button next to the box.

Consolidate

Where to find it
  • Data › Data Tools

Adds up figures spread over several ranges — often the same layout repeated on different sheets — into one summary. Use it for monthly or per-branch sheets that share a shape.

  1. Click the top-left cell of the empty area where the summary should go.
  2. Click Consolidate.
  3. In the dialog that opens, add each range you want to combine.
  4. Choose how the values should be combined.
  5. Confirm to write the summary.

Tip Leave room below and to the right of the cell you picked: the summary is as wide and as tall as the ranges behind it.

Data Model

Where to find it
  • Data › Data Tools

See the tables your workbook carries and declare how they relate: which column of one table names the key of another. Every relation is measured against the data as it is now — how many rows find a match, how many values are left over, whether the key still holds.

  1. Click Data Model.
  2. Read the tables: each row says where it sits and how big it is.
  3. Click New Relationship, pick the column that names (the many side) and the key it names.
  4. Confirm: the new row says how many rows are matched.
  5. Or click Auto-Detect to let the data propose the relations it supports.

Tip The key side lists only columns that repeat no value and leave no cell empty — so a relation the data cannot hold cannot even be composed.

Analyze Data

Where to find it
  • Data › Data Tools

Measures what your data actually says — which category dominates, which value is out of scale, which figure is rising — and shows each finding beside the chart that proves it. It is not the same question as Recommended Charts, which picks a chart that suits the shape of your data: this one looks at the values.

  1. Click a cell inside your data, or select the range you want to look at.
  2. Click Analyze Data.
  3. Read the findings on the right and click one to see the chart that proves it.
  4. Click Insert chart to place that chart on the sheet.

Tip The chart is inserted with the finding as its title, so it keeps saying what it demonstrates. If nothing is found, the window still opens and tells you which of the four reasons applies.

Group

Where to find it
  • Data › Outline

Ties the selected rows or columns together so they can be folded away and opened again in one move. Use it to hide the detail of a long sheet while keeping the totals in view.

  1. Select the rows or the columns you want to gather.
  2. Click Group.
  3. Use the outline control that appears beside the sheet to collapse or expand the new group.
  4. In the menu, Group opens a submenu with the further grouping choices.

Tip Groups can sit inside groups: gather a short run first, then gather the wider block that contains it.

Ungroup

Where to find it
  • Data › Outline

Takes the selected rows or columns out of their group. Use it when an outline no longer matches the way the sheet is laid out.

  1. Expand the group first, so nothing is left folded away.
  2. Select the rows or columns you want to release.
  3. Click Ungroup.
  4. In the menu, Ungroup opens a submenu with the wider clearing choices.

Tip Ungrouping removes the outline only; the rows and their values stay exactly where they were.

Subtotal

Where to find it
  • Data › Outline

Inserts a total after each block of rows that share a value. Use it to summarise a list without writing the formulas by hand.

  1. Sort the data by the column that defines the blocks, so equal values sit together.
  2. Click a cell inside the list.
  3. Click Subtotal.
  4. In the dialog that opens, choose the column that starts a new block and the columns to total.
  5. Confirm to insert the total rows.

Tip Sorting first is what makes this work: on unsorted data you get a total every time the value changes, which can be every row.

Show Detail

Where to find it
  • Data › Outline

Opens a collapsed group so the rows or columns inside it are visible again. Use it when you have folded a sheet down to its totals and want to look inside one block.

  1. Click a cell in the collapsed group you want to open.
  2. Click Show Detail.
  3. Read the rows or columns that have come back into view.

Tip The outline controls beside the sheet do the same thing with a single click, group by group.

Hide Detail

Where to find it
  • Data › Outline

Folds a group away so only its total is left showing. Use it to shorten a long sheet down to the figures you are actually comparing.

  1. Click a cell in the group you want to fold away.
  2. Click Hide Detail.
  3. Check that the total line is still visible.
  4. Click Show Detail when you want the rows back.

Tip The rows are out of sight, not removed: formulas that refer to them go on working as before.

Get Data

Where to find it
  • Data › Get and Transform

Starts a connection to data held outside this workbook, so the sheet is filled from the source instead of typed by hand. Reach for it when the figures live in a file, a database or on the web.

  1. Click the cell where the imported data should start.
  2. Click Get Data.
  3. Choose the kind of source you are importing from.
  4. Follow the prompts to point at the source and confirm the import.

Tip For a plain text or CSV file, From Text/CSV takes you straight there without the list of sources.

From Text/CSV

Where to find it
  • Data › Get and Transform

Reads a text or CSV file into the sheet, one field per column. It is the short route for the commonest import.

  1. Click the cell where the imported data should start.
  2. Click From Text/CSV.
  3. Pick the text or CSV file you want to import.
  4. Confirm the import to place the data in the sheet.

Tip If the source is not a text file — a database or a web page, say — use Get Data instead, which covers every kind of source.

From Web

Where to find it
  • Data › Get and Transform

Reads a table from a web page into the sheet and keeps it re-readable: the address and the table you picked are saved with the workbook, so Refresh All reads the page again.

  1. Click From Web.
  2. Paste the address of the page and click Connect.
  3. Pick one of the tables that were found: each row says how many columns and rows it has and what its headings are, and the preview beside it shows the table itself.
  4. Click Load to write it to a new sheet, or Transform Data to reshape it with a chain of steps first.

Tip The page is read by your Merydion server, not by the browser, so you need to be signed in. If the page later changes and no longer has that table, Refresh All leaves the sheet with the data it already had.

From Table/Range

Where to find it
  • Data › Get and Transform

Feeds data that is already in this workbook into a query, so you can reshape it without retyping it. Use it when the source is a table or range on one of your own sheets.

  1. Select the table or the range of cells you want to use.
  2. Click From Table/Range.
  3. Confirm the range that is proposed.
  4. Work on the data, then return the result to the sheet.

Tip Unlike the other sources, this one needs no file: everything it reads is already open in front of you.

From Picture

Where to find it
  • Data › Get and Transform

Reads the numbers out of a picture of a table — a file, or a screenshot on the clipboard. Before anything enters the sheet it shows what it read beside the picture, so you can check it.

  1. Click the cell the data should start from.
  2. In the menu, From Picture opens a submenu: choose whether to take the picture from a file or from the clipboard.
  3. Compare the table that was read with the picture: cells the program is unsure of are marked in both halves.
  4. Correct what needs correcting by typing in the cell, or use Review next to step through the doubtful ones.
  5. Click Insert data.

Tip The data enters exactly as you read it in the window. If a cell arrives as text instead of a number, the picture uses number separators the workbook does not recognise.

Recent Sources

Where to find it
  • Data › Get and Transform

Lists the sources you have already imported data from, so you can go back to one without hunting for it again. The list is yours and follows you from workbook to workbook.

  1. Click Recent Sources.
  2. Pick a source in the list; the line underneath tells you what kind it is and which workbook it came from.
  3. Click Reuse to start a fresh import from it.
  4. Use Remove from list to drop a source you no longer need.

Tip A range or a table only exists in the workbook that holds it, so a source from another workbook stays listed, says so, and cannot be reused until you open that workbook again. A text file can always be reused: the desktop app reads it straight from disk, the browser asks you to pick it again.

Existing Connections

Where to find it
  • Data › Get and Transform

Lists the data connections this workbook already holds and brings one of them in again, so you never build the same query twice. The preview beside the list shows the data each connection returns before you commit to it.

  1. Click Existing Connections.
  2. Pick a connection from the list and read, in the preview, the data it brings.
  3. Choose where the data should go: the sheet the connection already fills, or a new one.
  4. Click Load to bring the data in.

Tip Loading into a new sheet leaves the original data where it is and creates a second connection with the same definition, so Refresh All keeps both up to date.

Refresh All

Where to find it
  • Data › Queries, Types and Forecast
Ctrl+Alt+F5

Re-reads every connection in the workbook, so imported data matches its source again. Use it when the file or database behind the figures has moved on since you imported.

  1. Open the workbook whose imported data you want to bring up to date.
  2. Click Refresh All.
  3. Wait for every connection to finish reading its source.
  4. Check the imported data: it is now up to date.

Tip Only ranges that arrived through a connection are re-read; cells you typed yourself are left alone. It also refreshes every PivotTable in the workbook (also with Ctrl+Alt+F5); the ones that can't be read again are listed in an alert.

Queries & Connections

Where to find it
  • Data › Queries, Types and Forecast

Shows everything the workbook carries in the way of data that comes from somewhere else: the queries you built with Get & Transform and the connections the file declares. Each row says how many rows it loaded and where, or what it is missing.

  1. Click Queries & Connections.
  2. Read the list: each row carries the name, where it reads from and what it loaded.
  3. Pick a row and click Refresh to read the source again and rewrite the data where it already sits.
  4. Click Delete to drop the definition: the data it already loaded stays in the sheet.

Tip A connection badged “Not managed here” is declared by the file but Merydion does not run it: it stays in the list and travels untouched when you save again.

Properties

Where to find it
  • Data › Queries, Types and Forecast

Opens the properties of the query or connection the command acts on: the one whose loaded data holds the cursor, otherwise the one chosen in Queries & Connections. Here you change the name and the description, which travel inside the file, and decide whether Refresh All should re-run this query too.

  1. Click Properties.
  2. If the cursor is inside the data a query loaded, the window opens on that query; otherwise on the one chosen in Queries & Connections.
  3. Change the name or the description, and clear "Refresh this query with Refresh All" if you do not want the command to re-run it.
  4. Confirm with OK: the three properties are written together, and a single Ctrl+Z puts them back.

Tip A query left out of Refresh All can still be refreshed on its own: pick its row in Queries & Connections and click Refresh. A connection declared by the file is read-only: the window shows its kind and address and states that Merydion does not run it.

Stocks

Where to find it
  • Data › Queries, Types and Forecast › Data Types

Turns the text of the selected cells into stock data. Merydion recognizes the instrument from the name or the ticker you wrote, and writes what you choose in the columns beside it. Every price is written together with the day it was read, so a workbook reopened months later still says how old its numbers are.

  1. Write the names or the tickers of the instruments in one column.
  2. Select that column and click Stocks.
  3. Read what the window recognized: names it could not resolve are listed, and an ambiguous one is never resolved for you.
  4. Check the fields to write, then click Write.

Tip Prices come from the price feed your administrator configured. Without one the window still opens and says so: name, symbol, exchange and currency can be written anyway, because those come from the catalogue Merydion ships and work offline.

Currencies

Where to find it
  • Data › Queries, Types and Forecast › Data Types

Turns the text of the selected cells into exchange rates. A rate is an ordered pair — USD/EUR is how many euros one dollar is worth — and its second half is usually not written in the cell: you choose it in the window, once for the whole selection. The fields you check are written beside, and every rate carries the day it was read.

  1. Type the currency codes (USD, GBP) or the full pairs (USD/EUR) in a column.
  2. Select the column and click Currencies.
  3. Choose the currency to convert into: the rows that name a single currency will use it, while a pair already written in the cell does not change.
  4. Check the fields to write and click Write.

Tip The rates come from the feed your administrator has configured. Without a feed the window still opens and says so: pair, codes and currency name can be written anyway, because they come from the catalogue Merydion ships. A row already in the chosen currency is worth exactly 1, and that number needs no feed.

Geography

Where to find it
  • Data › Queries, Types and Forecast › Data Types

Turns the text of the selected cells into geographic data. Merydion recognizes the place from the name or the code you typed — against the geography it ships, so it works without a connection — and writes the fields you choose beside it. Every estimated figure is written together with the year of its estimate, in the column right next to it.

  1. Type the place names in a column: countries, or US states.
  2. Select the column and click Geography.
  3. Look at the map: the recognized regions are highlighted, so you can see at a glance whether Georgia was read as the country or as the US state.
  4. Read what was not recognized: unresolved names are listed, and an ambiguous name is never decided for you.
  5. Tick the fields to write. The window says how many columns it will write and how many non-empty cells it will replace.
  6. Click Write: the block is written beside the selection, and a single Ctrl+Z removes it all, headings included.

Tip The geography is shipped with the application, so it works offline and a workbook reopened in a year writes the same values. The fields offered depend on what was recognized: a country has thirteen, a US state five.

What-If Analysis

Where to find it
  • Data › Queries, Types and Forecast

Works a model the other way round: instead of reading the result, you say what the result should be, or what the inputs might become, and see what follows. Use it for questions of the form "what would it take to get there?".

  1. Build the model first, so the figure you care about is a formula over the input cells.
  2. Select the cell holding that figure.
  3. In the menu, open the What-If Analysis submenu.
  4. Choose the kind of analysis you want to run.
  5. Fill in the dialog that opens and confirm.

Tip The analysis follows the formulas only: an input the result does not actually depend on changes nothing.

Forecast Sheet

Where to find it
  • Data › Queries, Types and Forecast

Extends a series you already have into the future and puts the projection on a sheet of its own. Use it on figures recorded over time, such as monthly sales.

  1. Select the data the forecast is based on: the time points and the values recorded against them.
  2. Click Forecast Sheet.
  3. In the dialog that opens, set how far ahead the forecast should reach.
  4. Confirm to create the sheet holding the projection.

Tip A forecast is only as good as the history behind it: treat the projection from a short or irregular series with caution.

Solver

Where to find it
  • Data › Queries, Types and Forecast

Searches for the input values that bring a result to the target you set, while respecting the limits you impose. Use it for planning problems — a budget, a mix, a rota — where several answers look plausible.

  1. Build the model so the goal is a formula over the cells Solver may change.
  2. Click Solver.
  3. In the dialog that opens, name the goal, the cells that may change and the constraints.
  4. Run it, then read the values written back into the sheet.

Tip When no answer comes back, the constraints are usually the reason: relax them one at a time to find the one that cannot be met.

Data Analysis

Where to find it
  • Data › Queries, Types and Forecast

Runs a whole statistical procedure over a range and writes out the results, instead of leaving you to assemble them from single functions. Use it when you need a standard analysis rather than one figure.

  1. Arrange the data you want to analyse in a range.
  2. Click Data Analysis.
  3. In the dialog that opens, choose the analysis you want and point it at the range.
  4. Confirm, then read the results where they were written.

Tip Check where the results are to be placed before you confirm, so they do not land on top of data you still need.

Back to the guide

Productivity, made in Italy.

Bring the whole suite to your team — simple, secure and entirely yours.