How can I make Excel look more professional and feel more like a website or phone app?

At Excel4Business, our experts have learned a number of tricks to make an Excel spreadsheet feel more like a web app. We generally do so to minimise the training required to use our solutions. A summary of the areas we typically work on is shown in the table below:

What are the quickest ways to tidy up an Excel spreadsheet?

Hiding row/column headings and gridlines from the View ribbon will quickly remove visual distractions from your Excel sheet as well as increasing the amount of data that can be seen without scrolling. Where gridlines are helping users follow data from left to right, consider using subtler dashed borders or striped Excel tables in their place.

Sometimes it’s hard to read across an Excel sheet if the designer hasn’t thought through how the text is aligned. By default, Excel moves text to the bottom of a cell which can lead to a mismatch when reading between columns. Likewise, Excel will put numbers on the right and text on the left. Where a narrow column has a written heading but contains numbers, it is often more attractive to centre align the text so as to provide consistency from top to bottom. Text alignments can be quickly changed from the home ribbon.

All the above changes will make your spreadsheet feel less like Excel because they are the visual cues to users that they’re working in Excel. Visible rows/columns are like an artist’s paintbrushes and palette, whereas Excel’s default settings are the unmixed paints straight from the tube. A quick well-considered tidy up can achieve as much for your spreadsheet as moving a painting from an artist’s studio to an art gallery.

The disadvantage of hiding Excel’s design tools is that Excel spreadsheets are generally works in progress. To add a new expense category to your accounts sheet, you may need to insert a row. That may require checking the box to display headings again and, if another person is using your spreadsheet, they may not know how to do that.

At Excel4Business, our experts turn that disadvantage into an advantage. If we’ve added an ‘Add Expense Category’ button to a worksheet then we are encouraging users can add rows for that specific purpose. However, by hiding row headings, we would discourage users from inserting or deleting any other rows from the file which would prevent formulas from getting broken. If the intention of a tidy up is to give Excel a web feel then the quick wins can’t always be used in isolation.

How do I ensure a user only changes the cells they’re supposed to in my spreadsheet?

You should unlock the cells you want them to change and protect the sheet from the Review ribbon so they can’t change any others. You can add a password but, if you’re only concerned about accidental damage, this isn’t required. In an ideal world you would prevent users from clicking in the cells you don’t want them to edit by unchecking the ‘Select Locked Cells’ checkbox when applying the protection. This means they can quickly tab through the cells they are allowed to change.

If you want to be able to protect formulas whilst leaving them visible to other users, then those cells need to remain selectable. The solution is then to visually show which cells can be edited. Our consultants like white for unlocked cells with a variety of neutral blue/grey tones for other cells. If the sheet needs printing, you can keep the overall background white by ensuring all unlocked cells have some sort of border. This approach of using neutral colours works well if you also want to highlight any incomplete cells red using conditional formatting.

In some spreadsheets you may want to selectively lock a cell. Imagine an order form where the customer can select the colour of certain products. Unfortunately it is not possible to selectively lock a cell without using Office scripts or a Visual Basic macro. A more accessible workaround is to apply conditional formatting to shade a cell and discourage a user from typing in it but it is not as secure as protecting the cell. It also starts looking untidy if whole blocks of your spreadsheet need to be greyed out.

The other common fail case for protecting worksheets is that you cannot sort ranges containing protected cells. Basic sheet protection has its limits and an Excel developer would write code to provide protection against unwanted edits whilst maintaining access to other useful functions.

What alternatives are there to scrolling and tab navigation in Excel?

If you want a user to navigate your Excel file quickly, you can add hyperlinks to cells. These are identical to the links found on websites in that they let a user jump from one place in a file to another. Excel4Business tends to see internal links used in Excel files containing 20+ sheets where users want to be able to access individual project worksheets from cells containing their names in a summary table. You can also link to other documents if you want to be able to access other documents such as technical drawings or invoices from a single master sheet.

The main reason to scroll up and down individual worksheets is because they contain a large table of information. Filtering is often faster. The widest spreadsheets tend to be those where users have been collecting monthly data for years. On those, it makes sense to hide historic data, group by annual totals, or just freeze panes such that columns providing context on the left-hand side remain visible at all times.

The above alternatives can make a file faster to use. If your goal is to make a spreadsheet feel more like a web page then you will want to hide tabs from the bottom of the workbook. You can create a set of navigation buttons at the top of a sheet using textboxes and then freeze panes such that the navigation remains visible as a user scrolls down the page. Buttons can be assigned a hyperlink the same as individual cells. You can also highlight the button corresponding to the currently selected sheet on each sheet of the file. This is a neat trick for building a dashboard or management report.

The final alternative to tab navigation is to get rid of most the sheets in your file. Imagine a spreadsheet containing billing records for over 100 projects. The figures for each project are presented in the same format but the accounting department like to keep separate written notes on each project. The Excel oriented solution would be to have a separate sheet for each project. A website would simply have a dropdown menu at the top of the page from which you could select any project.

An Excel expert can use macros to mimic the more user-friendly web approach. One method would be to filter a single main table containing figures whilst pulling through written notes from a separate hidden table. Although it can be complex to set up, there are benefits to keeping all your figures in a single place, and it’s much easier to make changes to a single table as the business evolves.

How do I use different column layouts at the top and bottom of an Excel worksheet?

The simple answer is that you can’t, which is very annoying when trying to create documents containing addresses at the top, a table of products in the middle, and some set text at the bottom. The standard workaround is to merge cells so if you want two equal width columns containing long addresses at the top of the sheet with six narrower columns in the middle, one solution would be to set the widths of columns A/B equal to C/D/E/F and merge each row into the two sets of columns.

This poses the problem of whether it’s practical to divide the columns in an aesthetically pleasing way. The optimal solution often involves a column that is wide enough 90% of the time, but occasionally the text will need to spill over onto another row. Unfortunately the very cells that need to be wide are often merged cells and Excel does not know how to set the row height of a merged cell.

There are three workarounds. One is to set the text to shrink to fit. This can work if the text won’t shrink so far as to be unreadable. The second option is to increase the height of rows to cover the scenarios in which the text needs to wrap. This can look neat as long as the text doesn’t look anything more than double spaced. The other is to create a single hidden column of the same width as the merged cell, and use a formula to set the cell in the hidden column to equal the merged cell. As the duplicate column contains all the text in a single cell, Excel will automatically correct the height of the row.

Although our Excel consultants can generally find a way to make an Excel sheet look like a web page or Word document, sometimes the solution is not to try. If you need a Word document containing a table of information from Excel then it may be best just to populate Word from Excel using Visual Basic. If you want something that displays well when portrait with the limited width of a phone screen, Excel may be too constrained.

How can I speed up interactions with my management reporting spreadsheet?

Management reporting sheets often bring together large datasets and contain thousands of formulas referring to large ranges of data. An ideal solution is to replace intensive lookup formulas with pivot tables. Where that is not possible, you can tell Excel to re-calculate formulas manually or when the file is saved.

These days, an Excel dashboard is being mentally compared to people’s experience online. Users are generally pretty forgiving of a processing delay when they request three years of sales history, but less forgiving when they just want to know what happened last week. Websites are built by programmers who would generally optimise for speed on a task-by-task basis. The challenge in spreadsheet design is that formulas tend to slow based on how much raw data you have, not on how much data you need to look at for each individual task.

The best option is to use more efficient calculation methods. Pivot tables are cached in a computer’s internal memory which means they can calculate things far faster than Excel formulas. They are also designed to be used in dashboards. They contain shortcuts to displaying top 10s, they aggregate data by months and years, they can summarise lists of data into categories. The downside of pivot tables being cached is that they need refreshing each and every time someone changes the underlying data.

Another method with the same downside is to switch calculations off whilst editing the dashboard’s underlying data. That can require careful management to ensure no-one ever looks at stale data.

Most reports are prepared for managers who don’t have the responsibility for updating the underlying data. The reports are normally maintained on separate output sheets. Our consultants can write code to identify when users move from the back end sheets to the front end, and ensure all pivot tables and calculations are refreshed when required. That approach minimises the lag for anyone reading the reports.

How do I make my Excel charts more interactive?

A default Excel chart will only display data that is currently visible to a user. The easiest way to make an Excel chart more interactive is to let users filter the underlying chart data which is best achieved using slicers. Slicers can be used to filter data by categories or quickly select a range of dates. They look and feel like touchscreen buttons so look more professional than traditional pivot charts.

Excel slicers can be used on any Excel table. A simple example would be a table containing a Transaction Type column, where you have both incomes and expenditures. A slicer would provide two buttons, one for income, and one for expenditure. Clicking the buttons filters the underlying table. The slicers can be placed on another sheet next to a chart that is based on the Excel table. The effect of using the slicer would then be to change the appearance of the chart.

As slicers look like touchscreen buttons, our experts would recommend making the slicers feel like they’re part of the chart. One way to do that is to remove the border that would typically surround a chart so the slicers seem to occupy the same space. It is normally a good idea to make an interactive chart feel less like an Excel chart on the basis that people naturally consider Excel charts to be static.

The downside to using slicers is that you can only use slicers with Excel tables or pivot tables. That may require some messing around with the source data. Where the raw data is not in an appropriate format to be filtered, such as where product SKUs that need to be placed into more meaningful product groups, additional lookups may be required. It is worth stressing that a lot of this work would be required by any solution as users can only ever interact with data in its existing form.