voluntaryXchange

Excel Tip 42: Invisible Zeroes (and Other Stuff)

What if you need to add an invisible zero to a cell to make some other calculation work?

In this situation, the zero is not just a placeholder. It actually is part of the calculation and has to be there.

This post is mostly about the sophisticated workaround, but here’s the basic one first.

In most cases, you can add an invisible anything by making the color of the text match the color of the cell. In my case, I didn’t want to do this because the workbook used conditional formatting, and I’d have to match the colors in the formatting even when the formatting might change. Ugh.

The advanced trick is super easy.

• Type ALT H O E to get into the Format Cells dialog.
• Select the “Number” tab.
• Scroll to the bottom and select “Custom”.
• Into the text entry bar labeled “Type:” enter 3 semi-colons.
• Click OK.

Nothing will show in the cell because there is no value in there. But the cell will act as if there’s a zero there because of the custom formatting.

The only pitfall here is that if you do edit the cell to put a number in there, when you tap enter, the number will disappear. The cute thing is, it’s there and will work in the calculations. The workaround for this is to change the cell formatting back to something like “number”.

A Drum Machine On Excel

Add this to this list of unusual things people have done with Excel. Dylan Tallchief has programmed a drum machine using macros. While no doubt not as good as a commercial product, it’s nonetheless pretty fun to play with.

Download it here. Follow the link through to a Google Drive to get the file. It does require you to enable macros, so caveat emptor.

I’ve run this on Excel 2016, and the article below says it works on 2019.

Via Lifehacker.

Excel Tip # 41: Pitfalls Moving Page Breaks

I do not print from Excel too much. But sometimes ...

I have one database-ish worksheet, that I periodically filter and/or sort, and then select a range to print. It's easier to interface with another piece of software from a hard copy. It's also helpful to have the page breaks in the right place.

Today's problem was that they were not and I could not move them. I don't know about you, but I'm used to moving my page breaks around, as needed, in Page Break View.

The source of the issue is that Excel remembers where page breaks have been positioned, and I was sorting and filtering and printing just a selection of the worksheet.

What happens is that when Excel creates automatic page breaks for you, they are freely movable. You can tell where these are because they are dashed lines in Page Break View and Normal View.

However, as soon as you move an automatic page break it becomes a manual page break. These are shown by solid lines in the two views.

And here's the rub: manually placed page breaks can't be moved. They can be deleted and redone. So when I went to drag and drop them, they wouldn't drag, much less drop.

The solution is simple but kludgy. If you have a manual page break that's in the wrong spot, select the row underneath it, and click ALT P B R to remove it. Then you can either 1) insert a new manual break where you want it with ALT P B I, or 2) you can drag an existing automatic break to that spot and Excel will make it manual.

In my case, this was a tough one to figure out. The reason is that I'd sort and filter, print if I needed to, and then clear my sorting and filtering for the next time. But clearing the sorting and filtering does not clear the manual page breaks.

FWIW: a simple workaround for me is also to not save the file after each save and filter. It's a fixed database for me, so that's not a problem.

Excel Tip #40: Formatting for Percents That Are Numbers

Sometimes, mostly when importing data, you have numbers that are actually percents. For example, I just downloaded data from the World Bank’s Databank, and that’s the way they came.

There’s always going to be an arithmetic problem with this, since percents are decimals in a different format, and Excel won’t use them in arithmetic that way. So there’s always a consideration that maybe you should just divide them by 100.

But, if you’re going to keep them as is, there are formatting issues.

• If you format those as numbers, Excel won’t show the % symbol.
• If your format those as percents, Excel will multiply them by 100 (since 3 = 300%, and so on).

You could also use a custom format, but there’s a pitfall. Suppose you create the following picture code for your data:

00.0 %

You’d think that would work nicely, but unfortunately, Excel overthinks it, and you send up with the something that is multiplied by 100 again.

The trick is to prefix the % with a \, like so:

00.0 \%

The forward slash is a standard modifier that’s use in many programs, and at this juncture in Excel it means show the symbol, but do not do the calculation implied by the symbol.

How Management and Data Work

Last time I checked, the major weakness of decision making based on data was … making up the data you don’t know, and then not doing sensitivity analysis of those assumptions:

Wait … what? Don’t I know and love a certain university whose Provost did this with a policy change about increasing online enrollments?

From the January 7, 2016 Dilbert.

Harsh, but True

From Dilbert, October 8, 2016.

Justifying

This is how policymaking works:

I’m not kidding.

Hat tip to DB who pointed out that this is going on — really, they showed him the spreadsheet — with the great leap forward the administrators at our school want to take.

From Dilbert on October 7, 2016.

P.S. Given the date, I could imagine Pointy-haired Boss Democratic strategists thinking about how to beat Trump.

Excel Tip # 39: Worksheet Navigation

Sometimes I’m just dumb. It has annoyed me for a long time that if I split my worksheet vertically, it’s harder to use the worksheet tabs for navigation.

(Don’t get me started on who thought it would be a good idea to have splitting the worksheet also split the navigation slider at the bottom).

Anyway, you can switch between adjacent worksheets with CTRL+PageDown and CTRL+PageUp.

Excel Tip # 39: Link from a Worksheet to a Chart

This had never come up for me before last week. It seems straightforward. I always put hyperlinks in my worksheets to move big distances within them, but also to other worksheets.

But you can’t link straight to a chart that’s in its own worksheet. Somehow a “chartsheet” isn’t a worksheet any more.

The workaround is weird, but not too hard, and it works.

First off, how would you link within a worksheet. This is easy. Right click in a cell and choose hyperlink off the context menu. When the dialog opens, choose “Place In This Document” off the “Link to:” list on the left. Then give it an address. Simple, although there is a learning curve to getting the address just right in other worksheets.

Not so easy if you want to link to a chartsheet. You’ll need the name of that chartsheet too. Here’s the workaround.

• Create a new (empty) worksheet.
• Go to the cell where want the hyperlink (obviously, this won’t be in the new empty worksheet you just created). Create a hyperlink to any cell in the new empty worksheet. That hyperlink will have to have some string of characters that appears in that cell. Keep track of that hyperlink string.
• Now, hide the new empty worksheet.
• Go back to the worksheet containing the hyperlink. Right click its tab. Choose “View Code” off the context menu. Paste this little bit of VBA code.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

If Target.TextToDisplay = "hyperlink string" Then Sheets("ChartSheet").Select

End Sub

• Now save. Excel will balk. You’ve just added a little macro to your workbook, and it wants your permission to save it. This means changing the file extension from xltx to xlsm. No biggie.

It’s kind of a weird workaround. The fundamental problem is that Excel won’t let you link to a chartsheet at all. So you fake it.

What you’re doing is creating a hyperlink, and then making sure it doesn’t work. Then you’re adding a macro that will do the work when you click the link. That macro isn’t so much linking into the chartsheet so much as switching to the tab containing the chartsheet.

I learned this from the much fuller description at SumProduct.

Excel Tip # 38: The Screen Moves but My Cell Won’t

Today, when I used the arrow keys they didn’t work.

Well, they worked, but they did something odd: my selected cell would not move but its position on the screen would change.

Why is that? Using arrow keys to move around spreadsheets is pretty standard. But now the arrow keys were moving the spreadsheet.

It turns out, it’s not Excel, it was my keyboard. I had accidentally clicked the seldom used Scroll Lock key. I toggled it, and Excel was back to normal.

Thing is, now that I know that, I can see some use for this. I have some pretty big spreadsheets, and unfortunately, without some prior planning in the design of our spreadsheets our navigation to points off the edge begins with running the current cell all the way to that edge and then continuing further to “push” the spreadsheet in the direction we want. Clicking Scroll Lock gets around that problem.

Technorati Tags: excel,tip,38,scroll,lock,arrow,keynesian