A spreadsheet is a traditional productivity tool used by everyone in various forms of computer software. Be it Accountants, Analysts or Professors, the use of a spreadsheet is widespread. Some businesses even rely on spreadsheets to run them without hiccups. SAP Analytics Cloud tightly integrates various spreadsheet features within the Table widget and Grid Page of a Story. In this blog let us see how you can work with spreadsheets within SAP Analytics Cloud.

Create Spreadsheets

You can access features of a spreadsheet when you create a table using Analytics Designer or while creating a Story. Also, the Grid Page that you create in Story is simply a spreadsheet. One simple way to create a blank spreadsheet is to create a table widget that has no models mapped. Even when the table is already mapped with a model, you can edit and choose No Model option.

You can enable Column/Row Headers from the quick menu for easily identifying cells (A1, A2, B1, B2, etc.) in a Table widget.

Headers and Formula Bar are enabled by default when you add a Grid Page whereas, for a Table widget, you can manually enable Formula bar under Tools in the top menu.

Spreadsheet Features

You can simply enter text into a cell. You can copy and paste cell content from the clipboard. With the use of Formula Bar, you can also enter formulas including cell references. With the help of Styling Panel, you will also be able to merge cells. Basic Formatting like font type, font style, text alignment, text-wrap, number formatting and many more are possible within Styling Panel. You can even create predefined styles for formatting cells. Drag and Drop Functionality can be used to multi-select cells and auto-pattern filling of cell values. When you hover over the edges of the table widget, you can also add or delete rows and columns.

Adding Data

You will be able to map one model per Table and configure rows and columns within the Designer panel. When it comes to Grid Page you will be able to add multiple Tables. When you add a new dimension or expand a hierarchy in Table Widget, the width of the rows readjusts to display the table compactly. Whereas for Grid Pages, when you add a new dimension or expand a hierarchy, data expands to new rows/columns. Any newly added table in Grid Page will always be placed at the bottom of the previously added table. In Table widget, as soon as you add data, the default theme changes to reporting which can be later changed from the Styling Panel. Once data is added various pre-defined calculations are possible as well.

You can multi-select columns to add pre-defined formulas like percentage difference.

Formula Bar

Formula Bar supports various common functions that can be used in combination. The following are some of the functions identified.

Formula Bar creates a gateway to creating many custom calculations and formulas that you normally do in a spreadsheet. The snapshot below illustrates ABC analysis done on products using a nested if condition.

Some other common spreadsheet functions like count() are missing, though you will be able to achieve it using Story Calculations.

Cell Locking and References

Another interesting feature is the ability to set and unset read-only for cells. It restricts you from directly editing the cell values. When you copy a table cell that has data obtained from the model, cell references are automatically created. You can enable Show References option to view the link and if needed remove the cell reference. There is also an option to display formulas instead of calculated values.

Check out our blog series here to learn more about SAP Analytics Cloud.