Capital Font To Small Font In Excel

[Margaret Sigars]

Conradwould like to mimic the "small caps" capability of Word within Excel. The only way he can think to do this is to develop a macro that steps through every character in a cell. If the character is lowercase and 11 pt, then the macro should change the character to uppercase 9 pt. If the character is uppercase (or not a letter), then it should not be affected. Problem is, Conrad doesn't know how to affect the formatting of individual characters in the cell in the manner described.

When using a macro to affect only certain characters within the cell, it is good to remember that each cell has its own Characters collection that can be accessed and modified. Each element in the collection represents, as you might expect, a single character in the cell.

This allows us to put together a macro that examines what is currently in the cell, and if the character is currently lowercase, convert just that character to uppercase and reduce that character's font size.

This macro does its work on whatever cells are selected when it is run. It checks to make sure the cell doesn't contain a formula (formulas are skipped), and then it makes any modification to lowercase characters in the cell.

There are drawbacks to using a macro such as this, and you should be aware of them. The biggest drawback is that it actually modifies what is in the cells. When it is done, the cells will contain all uppercase text, even though the formatting may make it look like small caps. This means that you may have problems when you later run the macro a second time, and Excel's proofing tools (such as the spell checker) won't work on words that are all uppercase. (Excel can, however, be configured to still spell check such words.)

Just download the font you want (from this or any other reputable source) and install it on your system. When you restart Excel, the font should be available for formatting cells. Cells formatted to use such a font would show text as small caps, even though the actual cell contents are a mixture of upper- and lowercase.

One thing to be aware of if you go the route of using a small-caps font: If you share the workbook with someone who doesn't have that font installed on their system, there may be problems. The workbook won't be "hurt" in any way, but the information in the workbook will probably not display correctly as Excel will substitute a default font in place of the one you chose.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

I often have very limited space when creating reports and dashboards for users. I usually use Arial, or Arial Narrow, but UI isn't my area of expertise, so I want to know, how do you determine an optimal font for fitting the most readable text in the smallest space?

I did a rudimentary by creating a program that iterated through all of the available fonts I had installed on my Windows box at the time and printed a line containing each printable ascii character on to the screen in each of these font's. I repeated the test as well with different font sizes.

The results as I recall them were that Segoe UI and Tahoma were the best with respect to space utilization and readability for UI purposes at 10pt and 9pt sizes. In the short term we settled on Tahoma since Segoe UI isn't freely available for operating systems below Windows Vista. If you don't need to support Windows XP or older an Windows OS or other a non Windows OS then I would definitely go with Segoe UI otherwise I would go with Tahoma if it's available and if all else fails try Verdana. See this list for a lineup of available Windows fonts as well as information about the best of use of each.

Keep in mind as well that starting with Windows Vista I believe, Microsoft now recommends using a 9pt font instead of a 10pt font for UI elements since the Sego UI font displays much clearer than other fonts at low resolutions especially on flat panel displays.

Depending on what platform you are developing for, you may also want to look at modifying font metrics if possible. In .NET with WPF I recall there being quite a bit of ways to modify how the text is rendered to allow for condensing the space between characters and to make the individual characters more narrow. Using this type of technique you can stick with whatever font you like and just tweak it's rendering to get the results you need.

With regard to your specific example graph that you provided: for this particular graph I would recommend pivoting it so the text most likely to be read is horizontal for more natural reading. I would also place the number so that it is inside the each bar of the bar graph when it will fit with a color that stands out against whatever background color is there thus increasing the space for other things such as the labels. Laying out the bar graph as rows would make it easier to read and also to print on multiple pages if necessary. If a row layout is not possible then creating a separate key for each item in the graph would probably be reasonable and that way each bar could be place closer together as well to save space. The key would allow each bar to be labeled such as A, B, C... or 01, 02, 03... for example and the key (layed out in rows somewhere else) would give more detailed information about each.

Remember, a chart or diagram is mainly useful for getting quick visual information. If it becomes too much of a burden to the user/reader your probably best off simplifying it, consolidating some of the details of the chart, or just provide more raw data in a more tabular form.

Update: I've added another comparison below which shows a more complete listing of common characters including capital and lower case letters in each of the previously mentioned fonts with the addition of Verdana and MS Sans Serif (default UI font in Windows prior to Windows 2000). Unfortunately and in response to bobsoap's recommendation for using Verdana, it is pretty clear that Verdana is about the worst compared to the other fonts at 9pt although keep in mind that this may not hold true for other point sizes. Also size isn't necessarily always the most important detail, sometimes it's more important that a font is readable at small sizes than whether it is more compact relative to another font.

Generally, there's an "other way". The answers to this question are all great, but honestly, you should look at all the different option. Squeezing as much text in as little space as possible always means you've botched a previous design choice.

There are so many options, make sure you use the right one. Squeezing as much information into a single small area as possible is always the wrong way to go. Information needs space to breathe, to be readable, to be scannable and recognizable.

Yes, this generally means using way more space. But that's not a bad thing. It will take less effort for the person who consumes the data to quickly scan a couple of pages filled with well-structured information, than to figure out what that one bar means on that one-page-report. Think of the old board member, pocketing his reading glasses while passing the paper to the person next to him: "I can't read this - what does it say?"

There are assumptions in this question, the biggest one being that the "correct solution" to this UX issue is small text. But it's not. Small text becomes unreadable, an issue aggravated by tablets and other mobile devices. What if your user has bad eyesight? What if it's shown on an older, lower resolution monitor? What if there's glare from the sun or a light? So many reasons why small text can be unreadable.

Sometimes a smaller font is a good way out of a tight spot. In this particular case, at least for the part of the problem shown, there is a better solution which is both clearer, and takes half the space, like so:

Many fonts have been explicitly designed for use on computer screens (generally referred to as screen fonts). Matthew Carter's set of typefaces; Verdana, Tahoma, Georgia et al are great examples; they were designed from the outset with the pixel grid in mind. That makes them inherently good for use at smaller sizes (but generally makes them look a bit awkward in print).

In general, at small point sizes (remembering point sizes refer to height), I would recommend Verdana because it has a very high x-height which provides wide open counters. That will make it wider than some other fonts, which isn't what you're looking for (based on your examples).

Fonts like Segoe UI, Tahoma and MS Sans Serif are all designed as versatile screen fonts (being the default typefaces for Windows Vista/7, Windows XP and earlier versions of Windows respectively), so they're good all-around choices (with Segoe UI being the only one designed with sub-pixel hinting in mind and being the most modern choice).

There are two factors that should also be considered when selecting a font; if you're running the type vertically the sub-pixel antialiasing is naturally different, and generally the pixel hinting starts looking a bit worse (since type designers aren't likely to have tested their faces vertically). If you're running the fonts diagonally or otherwise not on the pixel grid, pixel hinting all gets thrown out the window.

3a8082e126