Office Q&A: Easy Excel navigation, Word table as a default, and a disappearing running balance

Learn to move quickly through hundreds of rows and columns of Excel, save a Word table as default and make the balance disappear.

  microsoft-hq -quarters-buiding-sign-logo-symbol-company.jpg

Image: Hans Engbers

This month, two readers posed questions that had easy solutions: Parvin asked for Excel navigation tips, and Lorraine wanted a predetermined table that resembled her needs. Unfortunately, a seemingly simple request from a reader named Susan does not have a dynamic solution, at least not one that does not require many hoops. In this article, I will show you two simple solutions and a more complex solution that does not always do the job.

I am using Office 365 on a Windows 10 64-bit system, but it can work with earlier versions. When working with the latest solution, you can work with your own data or download the demo files .xlsx and .xls.

MORE INFORMATION: Features and prices of Office 365 Consumer

Excel Quick Navigation

Parvin has a large sheet and needs a quick way to access the rows that are on the screen. Fortunately, there are many ways to move around a sheet: choose a favorite or use them all.

Pressing F5 displays the Go To dialog box. Simply enter a cell reference in the Reference control ( Figure A ) and click OK. F5 works equally well with a named range; Enter the range instead of a reference, and Excel will select the full range. (F5 does not work this way in the browser).

Figure A

  qa-a.jpg "data-original =" https://tr1.cbsistatic.com/hub/i/r/2019/ 09/04 / b628b6ac-394a-44fb-8dd7-0927efd88eac / resize / 770x / 4858416893315ca4abf76c9898747bb3 / qa-a.jpg

Use the Go dialog to quickly access a specific cell. [19659014] http://www.techrepublic.com/ "height =" 465 "width =" 620 “/>

The Excel name box works the same way and you can skip the F5 key; This control is at the far left of the formula bar. Enter a cell reference ( Figure B ) or the name of the range, and press Enter.

Figure B

  qa-b.jpg "data-original =" https://tr4.cbsistatic.com/hub/i/r/2019/09/04/d4b1a12d-7775-4a5e- 85ff-7b6289229b6b / resize / 770x / b4dc694b96f624226a00f32201a4531e / qa-b.jpg

Use the name box control to access a specific cell.

Get more navigation tips by reading How to use named ranges to quickly navigate an Excel workbook.

Word default table

Word table options are flexible and abundant, as Lorraine discovered, although by default, it starts with a fairly simple table. Each time Lorraine creates a table, she spends time modifying its properties, and uses the same properties for each table. If you make the same changes, or most of them, in each table, you can save time by changing the default table. When you create the new table, you will already have its most used properties and settings. (You cannot use this solution in the browser edition).

First, insert a table using the default table, Grid Table: it is the first thumbnail option in the Table Styles gallery. I could work with another table in the gallery, but I recommend that you keep the default. Once you have configured all the properties of the table, you will be ready to save it as default as follows:

  1. Select the modified table.
  2. Right-click on Table Grid in the Table Styles group on the Contextual Table Design tab.
  3. Choose Set as default.
  4. In the resulting dialog box, select All documents based on the Normal.dotm? ( Figure C ). If you keep the default value, what is this document only? Word will use the default format in the current document.
  5. Click OK.

Figure C

  qa-c.jpg "data-original =" https://tr1.cbsistatic.com/hub/ i / r / 2019/09/04 / 4f2504ab-e1f7-42da-ac0d-57582e240073 / resize / 770x / 7b4ea6de86cc06538b7b777ba312f607 / qa-c.jpg

Save your settings as the default table.

Total accumulated, sometimes

Susan has a sheet that evaluates deposits and withdrawals to calculate an accumulated balance. It works well, but occasionally has a row without deposit or withdrawal; when that happens, she would like the running balance cell to show nothing. At this time, repeat the balance of the cell above.

The expression of the current balance is simple:

  = previous balance + deposit - withdrawal 

For example, if the previous balance is $ 1,000 and on the same date, deposit $ 500 and withdraw $ 200, the balance Current would be $ 1,300: 1000 + 500 – 200 = 1300.

You may consider combining an IF () function and an AND operator to verify missing deposit and withdrawal values, but it will not work. If there is no value in the previous cell, the running balance expression returns an error.

Before showing you the solution, I want to mention that when you have blank rows in a data sheet, you should consider restructuring your data. In Susan's case, she may have other values ​​in the row, but sometimes she doesn't have to calculate balance values, so restructuring won't always be the answer. However, it is worth considering.

In this case, the solution is the conditional format. Specifically, when 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 background of the cell, adjust the font color accordingly.)

Now, let's configure 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 of the previous row. (The demo data is in a Table object, so the formula bar shows column names instead of cell references.)

  1. Select the balance column, E4: E9; Do not select the first cell of the column (E3).
  2. On the Home tab, choose New Rule from the Conditional Format drop-down menu (in the Styles group).
  3. In the top panel, select Use a formula to determine which cells to format.
  4. In the lower panel, enter the following formula:
    = Y ($ C4 = "http://www.techrepublic.com/", $ D4 = "http://www.techrepublic.com/" )
  5. Click on the Format tab and click on the Font tab.
  6. In the Color drop-down menu, choose white ( Figure E ) and click OK. Figure F shows the formula and format (which is not visible because it is white on white).
  7. Click OK a second time to return to the sheet ( Figure G ).

Figure D

  qa-d.jpg "data-original =" https: //tr1.cbsistatic .com / hub / i / r / 2019/09/04 / 580a5b13-165b-442a-8ec4-1a85118f02cb / resize / 770x / 740ab1d28038165473b2659359bbe711 / qa-d.jpg

use a conditional formatting rule to hide some results from running values.

Figure E

  qa-e.jpg "data-original =" https: // tr1 .cbsistatic.com / hub / i / r / 2019/09/04 / 435c91ae-e8e4-4ec9-a177-88f9ade93c82 / resize / 770x / 19734a315b2c1442c0ac4d8aa0b04ac3 / qa-e.jpg

Figure F [19659060] qa-f.jpg "data -original =" https://tr2.cbsistatic.com/hub/i/r/2019/09/04/6af55cd9-71c7-4fab-875e-a9fe2924d1a1/resize/770x/94ebd57c02d4b4ccbaa49817b48c131 /qa-f.jpg[19659061[qa-fjpg”/>

Combine this formula with the font color that disappears.