Learn how to move across hundreds of Excel rows and columns quickly, save a Word table as a default, and make a running balance disappear.
This month, two readers posed questions that had easy solutions: Parvin asked for Excel navigation tips, and Lorraine wanted a default table that resembles her needs. Unfortunately, a seemingly simple request from a reader named Susan doesn't have a dynamic solution—at least not one that doesn't require a lot of hooping jumping. In this article, I'll show you two simple solutions and a more complex solution that doesn't always get the job done.
I'm using Office 365 on a Windows 10 64-bit system, but you can work with earlier versions. When working through the last solution, you can work with your own data or download the demonstration .xlsx an .xls files.
LEARN MORE: Office 365 Consumer pricing and features
Quick Excel navigation
Parvin has a large sheet and needs a quick way to access rows that are screen deep. Fortunately, there are lots of ways to get around a sheet--pick a favorite or use them all.
Pressing F5 displays the Go to dialog. Simply enter a cell reference in the Reference control (Figure A) and click OK. F5 works equally as well with a named range; enter the range instead of a reference, and Excel will select the entire range. (F5 doesn't work this way in the browser.)
Excel's Name Box works the same way, and you can bypass the F5 key; this control is to the far left of the Formula bar. Enter a cell reference (Figure B) or range name, and press Enter.
Learn more navigation tips by reading How to use named ranges to quickly navigate an Excel workbook.
Word table default
Word's table options are flexible and plentiful, as Lorraine has discovered, though by default, you start with a rather plain table. Every time Lorraine creates a table, she spends time modifying its properties, and she uses the same properties for each table. If you make the same, or most of the same changes to every table, you can save time by changing the default table. When you create the new table, it will already have your most often used properties and settings. (You can't use this solution in the browser edition.)
First, insert a table using the default table, Table Grid--it's the first thumbnail option in the Table Styles gallery. You could work with another table in the gallery, but I recommend you stick with the default. Once you've set all the table properties, you're ready to save it as the default as follows:
- Select the modified table.
- Right-click Table Grid in the Table Styles group on the contextual Table Design tab.
- Choose Set as Default.
- In the resulting dialog, select the All documents based on the Normal.dotm template? (Figure C). If you retain the default, which is This document only? Word will use the format as the default in the current document.
- Click OK.
Running total, sometimes
Susan has a sheet that evaluates deposits and withdrawals to calculate a running balance. It works fine, but occasionally she has a row with no deposit or withdrawal; when that happens, she'd like the running balance cell to display nothing. Right now, it repeats the balance from the cell above.
The running balance expression is simple:
=previous balance + deposit - withdrawal
For instance, if the previous balance is $1,000 and on the same date, you deposit $500 and withdrawal $200, the running balance would be $1,300: 1000 + 500 – 200 = 1300.
You might consider combining an IF() function and AND operator to check for missing deposit and withdrawal values, but it won't work. If there's no value in the cell above, the running balance expression returns an error.
Before I show you the solution, I want to mention that when you have blank rows in a datasheet, you should consider restructuring your data. It's possible in Susan's case that she has other values in the row but sometimes no running balance values to calculate, so restructuring won't always be the answer. It's worth considering though.
In this case, the solution is conditional formatting. Specifically, when both the deposit and withdrawal values are missing, change the font color to white--that way, the value is there, but you don't see it. (If your sheet style uses a different color for the cell background, adjust the font color accordingly.)
Now, let's set that format using the sheet shown in Figure D. As you can see, there are no values to evaluate in row 7 so the expression in E7 repeats the balance from the previous row. (The demonstration data is in a Table object, so the formula bar displays column names instead of cell references.)
- Select the balance column, E4: E9; don't select the first cell in the column (E3).
- On the Home tab, choose New Rule from the Conditional Formatting dropdown (in the Styles group).
- In the top pane, select Use a formula to determine which cells to format.
- In the bottom pane, enter the following formula:
- Click the Format tab and click the Font tab.
- From the Color dropdown, choose white (Figure E) and click OK. Figure F shows the formula and the format (which isn't visible because it's white on white).
- Click OK a second time to return to the sheet (Figure G).
Oops… well, that's not exactly what you were expecting, was it? I did that on purpose. What seemed to be a rather simple request wasn't as simple as it seemed. The conditional formatting rule works, but unfortunately, the row's background is light blue, not white. The alternating row coloring is a product of the Table object's automatic formatting.
In Susan's case, she wasn't using a Table object, so the conditional formatting rule works fine; however, as I've shown, it has its limitations. The white font is less visible against the blue background than the black font, so it might still work. Trying to match the font color to the cell background color is more than a simple conditional formatting rule can handle.
The browser edition supports existing conditional formatting rules, but you can't create a conditional rule based on a formula in the browser.
How would you help this reader? Add your thoughts below in the comments section.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
- 50 time-saving tips to speed your work in Microsoft Office (free PDF) (TechRepublic)
- Cost comparison calculator: G Suite vs. Office 365 (TechRepublic Premium)
- Best cloud services for small businesses (CNET)
- Best to-do list apps for managing tasks on any platform (Download.com)
- How to use Excel's advanced sorting options (TechRepublic)
- How to add a drop-down list to an Excel cell (TechRepublic)
- How to use Windows 10 Task Scheduler to run Office 365 tasks (TechRepublic)
- Microsoft Office has changed, how you use it should too (ZDNet)
- More must-read Microsoft-related coverage (TechRepublic on Flipboard)