Formatting the web page from Excel

When you convert a spreadsheet to a calculating web page, the formatting is mainly copied from Excel. If your spreadsheet looks good, so will your web calculator. You can then apply a theme from the Theme Designer. 

One of the many strengths of the spreadsheet concept is the easy handling of rows and columns. When creating a spreadsheet in Excel you can tailor the exact appearance of each row and column, including

  • row height
  • column width
  • row/column border lines
  • row and column headings

Most of these settings are retained when you convert the spreadsheet to a web page. There are also numerous ways to format individual cells, including

  • how to align the content in the cell
  • what text fonts to use
  • how numbers are displayed
  • what colors and patterns to use for text and background
  • merging adjacent cells into one
  • conditionally format a cell depending on its content

Once again we will do our best to have the converted web page look like the original spreadsheet. If you find that the output of a conversion doesn’t exactly match your intentions, you may first want to go back to the Excel spreadsheet, adjust the formatting and convert the spreadsheet again until it is perfect.

When you’re done with the settings in Excel, have a look at the Theme Designer and see if using a theme can enhance the page further.

Excel defaults to watch out for

When you create a new spreadsheet with Excel, a large number of default settings are applied. Some of these may be obvious, others may depend on the situation, and a few are downright peculiar, in particular in a web environment.

Regional settings

Excel uses the regional settings in Windows to determine things like

  • The display formats for dates, including the first day of the week
  • The display format for times, including whether to use a 12- or 24-hour clock
  • The display formats for currency amounts, including separators and the default currency symbol
  • The display formats for numbers, including whether to use decimal point or decimal period and how to group digits for high numbers

The converted web page will automatically format dates, times, currency amounts and numeric values according to the regional settings that were in effect during the conversion. If you wish to create a web calculator for a different country or region, or for a global audience, you may want to change the regional settings in Windows before you perform the conversion.

The dreaded #######

If a cell is too small for its contents, Excel will truncate or wrap text. For numbers, however, you will get a warning if the contents don’t fit the cell – the entire cell is filled with number signs: #######. This is to avoid showing a partial number that may be misinterpreted as being the complete number.

Before converting a spreadsheet to a web page, ensure that all numeric fields are wide enough to contain all possible values.

Vertical alignment

By default, Excel has a rather peculiar habit of aligning rows at the bottom. If you insert a Calendar widget you may get the following result:

Screenshot of a widget with alignment at the bottom

 

This is because by default, Excel will align the cells at the bottom. Change the vertical alignment from Bottom to Top to make your spreadsheet easier to read:

Screenshot of a widget aligned at the top

Making widgets look good

Merging adjacent cells

Widgets use the same row/column layout as the rest of the spreadsheet. Many widgets require more space than the default row height and column width in Excel.

As an example, if you use a dropdown list, a listbox or radio buttons to select a city or state name from a list, you probably want the widget to be wide enough for the longest name in the list. If you don’t, SpreadsheetConverter must either wrap the longer names over more than one line, or truncate the names after the allotted width. Neither option really looks good.

If a widget is too big to fit within its cell’s height and width, the corresponding row and/or column may be widened automatically, depending on the web browser. This will probably harm the layout of your spreadsheet in an unpredictable way.

To make it easier for widgets to fit your spreadsheet, you should merge horizontally adjacent cells into one, giving the widget more space. In the example below, sixteen cells have been merged for the multi-line “Address” field.

Screenshot of an address field created by merginc cells.

You can merge cells exactly the way you want using Excel’s own Format Cells command. First select all the adjacent cells you want to merge, then right-click somewhere in the area and select Format Cells…. On the Alignment tab, check the Merge cells option. This will create one big cell from all the cells in your selection. Adjust the horizontal and vertical alignment if necessary. If you’ve merged a cell vertically, i.e. with one or more cells below it, select Wrap text for the text in the merged cell to flow nicely as a multi-line paragraph.

Screenshot of the Format Cells dialog

Optimizing column width

A column in Excel that has a width of 23 will occupy about half the screen width of an iPhone 3, 4, 5, 6 or 7 in portrait mode. As a rule of thumb, you can multiply the width of a column in Excel with seven to get the resulting column width on the screen, expressed in CSS pixels. 

That last part probably didn’t mean anything to you. And yet it’s a very useful thing to know if you wish to design a spreadsheet to exactly match a given screen width without zooming. If you are like most people, you don’t need this exact control of the layout. For you, we have provided an “automatic” option called Fit page to screen width on the Workbook tab. Just tick this checkbox and skip the rest of this section.

Screenshot of the Mobile setting in the Workbook tab of the task pane

Still curious? Let’s take it from the start.

The screen of a laptop, tablet or phone consists of pixels, the small physical points that together form the raster image on the screen. The density of these pixels directly affects the image quality – the more pixels per inch, the better the quality. Changing the pixel density usually does not affect the size of the objects on the screen. The Apple iPhone 4 has twice the pixel density of the iPhone 3 (and thus a much higher image quality), but it shows the objects in exactly the same size.

This is because web designers can express how big an object is to be on the screen, regardless of the physical pixel density. This is called logical pixels or CSS pixels. As an example, both the Apple iPhone 3 and iPhone 4 screen has a CSS pixel width of 320 in portrait mode. This means that an object with a width of 160 CSS pixels would fill half the screen width on both devices. It would look better on the iPhone 4 thanks to the higher pixel density, but it would be the same size.

In the table at http://www.mydevice.io/devices/ you can see the screen width in CSS pixels for many current handheld devices.

And now we’re back at where we started. The width of a column on the web page, in CSS pixels, is approximately seven times the width of the same column in Excel. So if you want a column to fill half the screen width of an iPhone 3, 4 or 5 in portrait mode, you should start testing with a column width of 23 in Excel. This is because the width of the screen is 320 CSS pixels, and half of that is 160, and divided by seven is 23. Or put the other way: a column in Excel that is 23 wide will become approximately 7 * 23 or about 160 CSS pixels wide on the screen, which is half the width of an iPhone 3, 4 or 5 screen in portrait mode.

A word of warning: once you’ve come this far, you’ll probably want to turn on the “auto” setting above anyway, because it also handles device rotation seamlessly. Switch to landscape mode and the contents still fit the now much wider screen. If you don’t enable this setting, your spreadsheet will appear with exactly the same width in landscape mode as in portrait mode, which may leave a lot of unused space to the right.

The browser effect

When your spreadsheet is converted into web format, you will lose most of the detailed control of the user experience you had when you designed it in Excel. As an example, most web browsers remove spaces as they see fit. If you position text by inserting extra spaces before or after a word, you will find that even though SpreadsheetConverter maintains the extra spaces in the converted web page, the browser simply discards them, which may give a totally different layout to the page.

Once converted into web format, spreadsheets also enter the realm of competing and sometimes incompatible web browsers. No browser is identical to the other, and a web page will inherently appear somewhat different in each browser and device. This is part of the natural behavior of the world-wide web.

When SpreadsheetConverter converts a spreadsheet for the web, it is forced to make certain compromises when it comes to formatting. When compared to the original spreadsheet in Excel, the web page created by SpreadsheetConverter will not be an identical twin regardless of which browser or device you use to view it. In fact, it will look slightly different in every browser, and often different between versions of the same browser. Once again, this is a natural consequence of entering the web environment. In most cases the result is good enough. Just remember to test your web page in as many browsers and devices as you can.