Structured tables, conditional formatting and data validation.
Quick Analysis
Where to find it
Data › Data Tools
Shortcut menu
Ctrl+Q
Quickly apply formatting, charts, totals, tables or sparklines to a selected range from one floating menu.
Select a range of cells that contains data.
Click the Quick Analysis lens at the bottom-right corner of the selection, or press Ctrl+Q, or choose Quick Analysis from the right-click menu.
Pick a tab — Formatting, Charts, Totals, Tables or Sparklines.
Hover a Formatting or Totals option to preview the result live on the sheet.
Click an option to apply it as a single undo step; press Esc or click outside to close.
Tip The lens appears only when you select more than one cell with data; options that do not apply to the selected data type are shown dimmed.
Structured tables
Turn a range into an Excel-style structured table with banding, a header row and built-in styles.
Select the range of data, including its header row.
Press Ctrl+T, or use Format as Table or Table.
Let Tabulys detect the header row when it creates the table.
Apply a built-in style from the list of light, medium and dark designs.
Right-click inside the table to add a totals row or convert it back to a range.
Tip Formulas can use structured references such as Table1[Column] and [@Column]; typing in the row below auto-expands the table.
Table Design and filters
When you select a table, every header cell gets a filter arrow for sorting and filtering its rows.
Click any cell inside the table.
Rename the table or resize its range, and use Summarize with PivotTable, Remove Duplicates, Insert Slicer or Convert to Range.
Turn Banded Rows, Banded Columns, Total Row, First Column, Last Column and Filter Button on or off.
Pick a look from the Table Styles list of light, medium and dark designs.
Click the filter arrow (▾) in a header cell to sort A→Z or Z→A, tick or untick values, apply Top 10, or type a text or number criterion.
Tip Filters are saved with the workbook, so the table reopens with the same sorting and filtering applied.
Conditional formatting
Select the cells to format conditionally.
Open Conditional Formatting.
Use Highlight Cells rules such as Greater Than, Less Than, Between, Equal To or Text that Contains.
In a Highlight Cells rule dialog, type the comparison value and pick the format from the list: each entry previews the colours that will be applied to the cell.
Apply Duplicate Values, Top 10 or Above Average directly.
Choose Data Bars, Colour Scales or Icon Sets to visualise the values across the range.
Open Data Bars…, Colour Scale… or Icon Sets… to edit a graphic rule and set custom thresholds: pick a Type — Automatic (min/max), Number, Percent, Percentile or Formula — and a Value for each stop.
For Data Bars also set the bar colour, the negative-values colour and the axis position (Automatic, Cell midpoint or None).
In New Rule… choose one of the ten rule types from a list where each one explains what it does, watch the result in the live preview (bold and italic really show), pick the colours from a grid, and use Applies to to retype the range in A1 notation (for example $A$1:$D$100, several areas separated by a space).
Open Manage Rules… to see every rule in scope with its priority number, its condition, the range it applies to, a preview of the format painted in the rule's own colours and a Stop If True check box you can tick right in the list.
Rules are applied from top to bottom and for each cell the first one that is met wins: change the order with Move Up and Move Down, while the window stays open.
Use Duplicate to place a copy of a rule right below the original, Edit Rule… to change it and Delete Rule, set apart from the other commands, to remove it.
A command you cannot use is greyed out — Move Up on the first rule, for instance — and Edit Rule… is off for rules the file carries but the editor cannot rebuild: its tooltip says why.
Confirm with Apply and Close: everything changed in the window enters the document as a single operation you can undo with Ctrl+Z, while Cancel writes nothing.
Tip The order of the rules matters: for each cell only the first rule that is met applies, and the preview in the Manage Rules… list says at a glance what format each one paints.
Data validation
Restrict what can be entered in cells, including a drop-down list of allowed values.
Select the cells to validate.
Open Data Validation.
Choose a type and criterion, such as a list of values, a number range, a date or a text length.
Optionally add an input message and an error alert.
Confirm; invalid entries are then blocked or warned about on entry.
Tip On a list cell, click the in-cell arrow or press Alt+down arrow to open the drop-down. The list can come from another sheet: in Value / Source type, for example, =List!$A$1:$A$3, or click the RefEdit button next to the box and select the cells on that sheet; the validation still goes on the cells the dialog was opened from.
Cell hyperlinks
Where to find it
Edit › Cells
Shortcut menu
Select the cell to link.
Press Ctrl+K, or click Link.
Enter the Text to display and the Address (http, https or mailto).
Confirm; the cell turns blue and underlined, with a hand cursor on hover.
Click it quickly to follow the link, or clear the Address to remove it.
Slicers
A slicer is a floating box of buttons that filters a PivotTable or a structured table with a click — a visual, always-visible filter.
Click inside a PivotTable or table, then click Slicer.
In the checklist, tick the fields you want a slicer for, then OK.
Click a value button to filter; click again to toggle it back.
Use the clear-filter icon in the slicer header to reselect everything.
Select the slicer to show Slicer Tools, where you set the number of columns and the style; drag the border to move it.
Tip Filtering from the slicer and from the on-sheet dropdown stay in sync; each click is a single undo step and the slicer round-trips when you save.
Timelines
A timeline filters a PivotTable or table by a date field, letting you pick a period on a track of tiles (days, months, quarters or years).
Click inside a PivotTable or table with a date field, then click Timeline.
Tick the date field in the checklist, then OK.
Click a tile to filter the data to that period.
Change the granularity (Years, Quarters, Months, Days) from Timeline Tools.
Use the clear-filter icon to show all periods again.
Tip The timeline highlights the selected period and round-trips on save; clearing it removes the date filter.
Forecast sheet
Project a historical series into the future with seasonal exponential smoothing (ETS): Tabulys creates a new sheet with the forecast table and a chart.
Select two adjacent columns: the timeline (dates or periods) and the historical values.
Click Forecast Sheet.
Check the source ranges and the options in the dialog (periods, confidence interval, seasonality, missing points, duplicate aggregation, chart type).
Click Create to generate a new sheet with the history, the forecast and the confidence band, plus a chart.
A single Undo removes the whole forecast sheet.
Tip Seasonality is detected automatically; set 0 for a linear (non-seasonal) forecast or type a number to force a period. The FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY and FORECAST.ETS.STAT functions are also available in formulas.
PivotTables
Summarize a range of data into a PivotTable that groups values, applies subtotals and a grand total, and reflows on demand — without touching the source range. The +/- buttons in front of group labels (or a double-click on the label) open and close the levels; layout, subtotals, totals and styles are set while the active cell is inside the PivotTable.
Select a range of data, including its header row.
Click PivotTable.
In the Create PivotTable dialog, keep New Worksheet (the default) to place it on a fresh sheet, or choose Existing Worksheet and type a Location to anchor it elsewhere, then click OK.
Tabulys puts the first non-numeric column on Rows and sums the numeric columns as Values, with subtotals and a grand total.
Drag fields between the Field List's Filters, Columns, Rows and Values areas, or right-click a value cell to change the summary function; refresh with Alt+F5 after the source data changes.
Tip Choosing New Worksheet creates the sheet and the PivotTable together as a single Undo step, anchored at cell A3 — the two rows above stay reserved for a page filter, as in Excel.
Group a PivotTable field
A PivotTable with a date or number field in Rows or Columns reads better by period or by range: group the dates by years, quarters, months or days, the numbers into ranges of equal width, or bring a few items together by hand. The table recalculates at once, and each grouping is a single undo step.
Right-click a cell of the PivotTable.
If there is a date field in Rows or Columns, choose Group by Years, Group by Quarters, Group by Months or Group by Days; Group by Years, Quarters & Months creates the three levels at once.
If there is a number field, choose Group Values…: the numbers are gathered into about ten ranges of equal width, chosen from the values.
To bring a few items together by hand, select two or more of their labels in the rows of the table and choose Group Selection: a new item, Group1, is created to hold them.
To return to the original items, choose Ungroup.
Tip Grouping acts on the first field in Rows, then in Columns, that contains dates or numbers, not on the field of the cell you click: to group another one, first move it in the PivotTable Fields pane.
Calculated fields and items
Add your own formulas to a PivotTable: a calculated field derives a value from other fields (Tabulys sums first, then computes), and a calculated item adds a row or column computed from the other items. GETPIVOTDATA extracts a pivot value by robust reference.
Right-click a PivotTable cell and choose Calculated Field… or Calculated Item….
Type a name and a formula (e.g. =Sales-Costs for a field, ='North'+'South' for an item).
Click OK: the field or item appears and recalculates with the pivot; a single Undo reverts it.
To edit, reopen the dialog with the same name; to delete, leave the formula empty.
In any cell, use =GETPIVOTDATA to read a pivot value by field and item.
Tip A calculated field always sums the referenced fields before applying the formula. Calculated items enter subtotals and grand totals, as in Excel; with calculated items the PivotTable summarizes only by Sum, Count, Max, Min or Product. GETPIVOTDATA returns #REF! when the cell is not inside a PivotTable.
Show values as (Running Total, Difference)
Beyond row, column and grand-total percentages, a value field can show each cell relative to a base field or item: a running total along a field, the difference from a base item, the percent of a base item, or the percent difference from it — all recalculated with the pivot.
Select a PivotTable, then in the Fields pane open the ▾ menu of a value field (in the Values area).
Choose Show Values As, then a mode: Running Total In, Difference From, % Of, or % Difference From.
In the submenu, choose the base field (a field on Rows or Columns) the calculation runs along.
For Difference From, % Of or % Difference From, also choose the base item to compare against (default: the first item).
The cells recalculate in place as one Undo step; choose No Calculation to return to raw values.
Tip The base item's own cells are blank for Difference From and % Difference From, and 100% for % Of, as in Excel. Index shows how much each cell weighs in the table — (value × grand total) / (row total × column total) — and needs no base field. Rank is not offered yet.
Solver
Find the values of one or more cells that maximize, minimize, or reach a target value in an objective cell, subject to a set of constraints.
Click Solver.
Set the objective cell (a cell that contains a formula) and choose To: max, min, or a target value.
Enter the variable cells (the cells Solver may change) — they must contain values, not formulas.
Add constraints such as B1<=10; B2>=0; C1=C2 (operators <=, >=, =), separated by semicolons.
Click Solve: Solver optimizes the model and writes the result to the variable cells as a single Undo step.
In the result dialog, choose OK to keep the solution or Cancel to restore the original values.
Tip If no solution satisfies every constraint, Solver keeps the best point found and reports the maximum violation. A range on the left of a constraint (for example B1:B3<=10) is expanded cell by cell. The Data Analysis (ToolPak) tools and integer/binary constraints are planned follow-ups.
Data Analysis (ToolPak)
Run the statistical tools built into Tabulys — Descriptive Statistics, Histogram and Regression — over a range of data and get the result written out as a report of cells, in the spirit of Excel's Analysis ToolPak.
Click Data Analysis.
Choose a tool: Descriptive Statistics, Histogram or Regression.
Set the Input Range (for Regression, the Input Range Y and the Input Range X); optionally choose Grouped By columns/rows and whether labels sit in the first row or column.
For Histogram, optionally set the Bin Range — leave it empty to let Tabulys compute the classes automatically.
Enter the Output Range, the top-left cell where the report starts, then click OK.
The tool writes the report as a block of static cells, undoable with a single Undo.
Tip The report is a snapshot: it does not recalculate when the input data changes — rerun the tool to refresh it. ANOVA, Correlation, Covariance, the t/F/z-Tests, Moving Average and Rank and Percentile are planned follow-ups.
This browser's session is closed: whoever uses it next will have to sign in with their own password.
Sign-out not confirmed
This tab is signed out, but the server didn't answer: the session may still be open in this browser. Try again, or close the browser before you leave this computer.
Still no answer from the server.
Signed out in another window
The session was closed in another tab or app of the suite, so this tab is signed out too.
Visit statistics
If you accept, we use Google Analytics to count visits and see which pages are useful. If you decline, or close this notice, nothing from Google is loaded and the site works just the same. Cookie policy