Making Excel Do Your Work for You

Ever wished you could just get a sub-set of the results in your report? Want to know how to get Excel to print your report on one page width? Ever wanted to use a formula is another section, but it refers to the wrong cell? Read on for some Excel tips and tricks. 

First things first 

If you always run your reports as PDFs, none of the tricks below will work. Once you click Next in ART, you can set up the file to save as Excel.  

Filtering 

Once you have your file downloaded, you can find a subset of your results, perhaps just the errors in one column using filters. To do this, first enable editing at the top, then: 

1. Highlight the header row, the one with the names of the columns 

2. Click the Data tab in Excel 

3. Click Filtering 

4. Click the triangle to access the filter for that column (you can filter on two columns at once) 

5. You can select one item, multiple items, or you can use text filtering and type a word or part of a word (e.g., missing) in the box 

6. If you want to clear the filter, you can either clear just that column by clicking the triangle again, or click “Clear” next to the Filter button on the Data Tab. 

Printing Excel files 

If you are printing a tab in Excel, and you would like it to print on one page width (or one page total) there are some tricks to do so. 

 You may want to change the page from printing portrait (the page is longest up and down) to landscape (wider than it is long) (see step 2), or you may just need to change the width (see step 3)  

1. Click the Page Layout tab 

2. Click orientation if you need to change the page from portrait to landscape or back 

3. Click width, you can change this to 1-page wide (you can also change length, but the print might get too small) 

Excel formula doesn’t copy or pull down well (Absolute Reference) 

If you have one cell you are referring to for all your formulas, you need to tell Excel that using an absolute reference.  

• If you are creating a formula that you want to fill down or copy elsewhere, but want the referenced cell to stay the same… 

  • Example, if you want percentages of all clients who are a demographic in the Core report 

• If you copy or pull down the formula to the rows below, you get nonsense 

• When entering the formula, you can either use the F4 key (or Function+F4 on laptop) or put a dollar sign in front of the row, column, or both to make an absolute reference 

• The formula pictured will always refer back to cell Q22 now, no matter what the numerator is in the formula