20 Spreadsheet features in Power BI for Excel fans

“As an Excel fan, I love the simplicity, flexibility and power of spreadsheets. I have everything under my control, and I can do what I want whenever I need to. I do like Power BI, but it has its own restrictions. For example, I cannot freely insert a row or edit data directly in the report. I therefore prefer Excel over Power BI for most or all of my reporting needs.”

Does the above accurately reflect your feelings towards Power BI and Excel? If so, you need to take a second look. With Inforiver delivering a variety of the spreadsheet-like capabilities inside Microsoft Power BI, things are not the same anymore.

Today we’ll look at twenty (20) features that make it very appealing for Excel fans to embrace Power BI.

1. Insert/edit/update values in cells

With Inforiver, you can update cell values directly in your table or matrix report. After any update, you will also see an edit icon next to the values that you have changed.

2. Formula with cell references

The most popular feature in spreadsheets is now available in Power BI. Insert a formula in a cell that refers to the contents of other cells.

3. Insert Rows & columns

You can insert rows & columns just as you can do with spreadsheets. These inserted rows & columns can be blank – where you manually type in values one cell at a time – or calculated using an advanced formula editor.

Note that as you type in the formula, the autocomplete feature looks up possible matches from the other rows. When I type in ‘La’, it shows Lamps and Laptops. The formula engine supports logical, Boolean, math functions and more.

Adding a calculated column follows a similar procedure. In this case we have created a Margin % column using a formula.

4. Value and background Formatting

Apply cell, row or column-level formatting using the toolbar options. You can increase or decrease font sizes, and even add custom borders.

5. Hide Rows & columns

You can hide rows or columns from the toolbar in a single click.

Once hidden, you can unhide them from the menu.

Another way to hide or unhide columns is to use the ‘Manage Columns’ options.

6. Reorder or custom-sort rows and columns

In Excel, you can cut and paste a row or a column and relocate it elsewhere. Inforiver does it better. Just click and drag the row or the column.

7. Group Rows & columns

Create visual hierarchies in rows & columns by selecting multiple items and grouping them. When you group rows, the subtotal values are calculated automatically.

8. Pin / Freeze rows & columns

This is very helpful when you have too many rows or columns in your report. Pin any row or column and freeze them respectively at the top or left.

9. Expand/Collapse row & column hierarchies (Pivot Mode)

With Inforiver, you can expand and collapse both row and column hierarchies. Note that the expand/collapse for columns is not supported by the native matrix visual delivered by Power BI.

10. Cell-level number formatting & scaling

Inforiver takes number formatting to a different level than what Excel or other BI tools like Tableau or Qlik can accomplish. You can apply any one of the scaling formats to your data in tabular or matrix reports in a single click.

• universal number formatting (applicable to all values in the table)
• measure-level formatting (e.g., sales in thousands, profits in %)
• auto formatting (where each cell is scaled independently of the rest)
• native format which reverts to the format specified in the underlying Power BI data model

11. Decimals and prefix/suffix

While this is a simple feature in Excel, it is typically not easy to format values in a specific cell (or a range of cells) in Power BI. With Inforiver, you can select cells and use the toolbar options to increase/decrease decimals. You can also add prefix / suffix – something that brings parity with Excel’s accounting and currency formatting properties - which are not available in native Power BI.

12. Notes & comments

You can add cell, row and column-level notes in Inforiver. These can show up on hover, or as a footnote below the table. The hover option is shown below.

The comments feature works in a similar fashion except that it can facilitate conversations & interactions with your peers. When a user is tagged in a comment, an email notification is triggered.

13. Text rotation/orientation in columns

This is very helpful when you have a lot of columns in your report. You do not have to miss this Excel feature in Power BI anymore.

14. Word wrap

Word wrap column headers to keep the column width optimized. This is very helpful when you have long column names or have too many columns.

15. Resize rows & columns (including pixel-perfect column widths)

In Power BI, you can resize row height and column widths, just as you are used to doing in Excel. Inforiver’s Rails feature reveals spreadsheet-like borders that you can click and drag to resize rows and columns.

You can also perform pixel-perfect column widths, something that is missing in native Power BI table and matrix visuals.

16. Horizontal and vertical value alignment in cells

Align values horizontally or vertically within a cell. Both options are available in the toolbar.

17. Indent text values

Use the indentation icon from the toolbar to indent specific values. This feature makes creating financial statements a breeze. Check out the first column containing indented values.

18. Status bar at the bottom (showing aggregation metrics)

Similar to Excel, you can use the status bar to quickly check aggregated metrics such as sum, average and count for a selected range of cells.

19. Column filtering

A key feature that many Excel users have come to miss in Power BI is the ability to perform data filtering for each column independently. Inforiver goes a step further, and provides filtering for columns, rows and measures too.

20. Keyboard shortcuts

Inforiver also delivers a set of keyboard shortcuts:

• Copy/Paste: Ctrl C/V
• Zoom In/Out: Ctrl +/-
• Undo/Redo: Ctrl Z/Y
• Ctrl/Shift based range navigation with arrow keys (up/down/left/right). E.g., Ctrl+Shift+right arrow key selects all the cells in the row starting with the current cell till end of the row.

There are many other Excel features that Inforiver delivers, but we will stop here. If you are looking to migrate your Excel reports to Power BI, give it a try with Inforiver today. Inforiver can also be purchased directly from Microsoft AppSource.