Upcoming webinar on 'Inforiver Charts : The fastest way to deliver stories in Power BI', Aug 29th , Monday, 10.30 AM CST.    Register Now

20 Spreadsheet Features in Power BI for Excel Fans

by Inforiver | Aug 16, 2022 |

“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.

20 Spreadsheet Features in 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.

Insert edit or update cell values

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.

Formula with cell references

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.

Insert Calculated Rows

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.

Calculated Rows

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

Insert Calculated Columns

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.

Value and background formatting

5. Hide Rows & columns

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

Hide rows or columns

Once hidden, you can unhide them from the menu.

Unhide rows

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

Show hide columns

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.

Reorder or custom sort rows and columns

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.

Group Rows

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.

Pin or freeze rows and columns

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.

Expand/Collapse Hierarchies

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
Number formatting

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.

Decimal value formatting

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.

Notes and Comments

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.

Comments and Conversations

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.

Text rotation

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.

Word wrap

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.

Resize rows & columns - pixel-perfect width

16. Horizontal and vertical value alignment in cells

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

Horizontal and vertical alignment

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.

Indent text

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.

Status bar showing aggregation metrics

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.

Column filters

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.


Inforiver Logo
IBCS Certified
Power BI certified
About Inforiver!

Inforiver drives business productivity and business performance with faster time to insights inside Power BI through an intuitive no-code experience. The product is developed by Lumel Technologies (formerly Visual BI), which has a decade of experience in building add-on solutions on top of BI platforms (SAP BI and Power BI) with our suite of innovative products such as ValQ, xViz, and BI Hub
Headquarters
5920 Windhaven Pkwy, #130
Plano TX 75093
ValQ
xViz
BI Hub
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram