Excel Tips, Tricks, and General Information

Conditional Formatting

Conditional Formatting can help find duplicate values in an Excel file.

To use Conditional Formatting to locate duplicate values in cells in Excel:

1. Open the Excel document.
2. Highlight the cells to be checked for duplicates.
   • To select an entire column, click on the column name (A, B, C, etc.).
3. Click on the Home tab at the top of the screen.
4. In the Styles section, click Conditional Formatting.
5. Choose Highlight Cells Rules.
6. Choose Duplicate Values.
7. Click OK.
8. All cells that contain duplicate values will now appear with a light red background and red text.

Fill Handle

The Fill handle in Excel has the ability to fill blank cells with a value that is already entered into the Excel file. If the same value is to be entered for each row of a column, the Fill handle can make data entry faster.

To use the Fill handle in Excel:

1. Open the Excel document.
2. Determine what cells need to be filled.
   • The Fill handle can only fill cells adjacent to the existing data.
   • If the cells that need to be filled are not touching the cell that contains the data, copying and pasting the information may be a better option.
3. Select the cell that contains the data that will be filled.
   • The Fill handle will attempt to predict what will be filled.
   • If the cell contains a numerical value, the Fill handle will incrementally increase the value in each filled cell.
   • If the numerical value that is being filled should not change, enter the data to be filled in two cells, then select both cells during this step.
4. Hover the cursor over the darkened square in the lower right of the selected cell.
   • The cursor should become a cross hair.
5. Click and drag the cursor over the cells to be filled.

Filtering

Filtering information in Excel will allow for easier viewing of the data, as the information can be sorted according to each column heading.

To filter data in Excel:

1. Open the Excel document.
2. Select cell A1.
3. Click the Data tab at the top of the screen.
4. In the Sort & Filter section, click Filter.
5. Each column heading will now become a drop down menu. Click on the arrow on the right side of the cell to sort the information.

Find Blank Cells

Each import file has columns that cannot contain blank values. These columns will be indicated by red column headings in the import file.

To find blank cells that may be causing import errors:

1. Open the Excel document.
2. Select the cells to be checked for blank values.
   • To select an entire column, click on the column name (A, B, C, etc.).
3. Click the Home tab at the top of the screen.
4. In the Editing section, click on Find & Select.
5. Choose Go To Special.
6. Choose Blanks.
7. Click OK.
8. All blank cells will now be selected.

Find and Replace

The Find and Replace feature in Excel can locate and replace specified values in the Excel document with another value. Find and Replace can be used in an instance where a value is repeated on an Excel sheet, but all values need to be replaced with another value.

To use the Find and Replace feature:

1. Open the Excel document.
2. Click on the Home tab at the top of the screen.
3. In the Editing section, click on Find & Select.
4. Click Replace.
5. In the Find What field, enter the value to be replaced.
6. In the Replace with filed, enter the value to replace the existing value with.
7. Click Replace All to change all found values, or Replace to replace each value one at a time.

Mark Checkboxes

To mark a checkbox on a W-2 and/or 1099 form when importing, the field must be contained in the Excel file. The field must then be populated with specific codes. These codes will tell the program whether to check the box or not to check the box.

The following codes, or any combination of them, can be used in an Excel file to mark checkboxes on forms when importing:

• Y or N
• Yes or No
• 1 or 0 (zero)
• T or F
• True or False
• X or left blank

Maximum File Size

The maximum Excel import file size that can be imported into Yearli is 50MB.

To check the size of your import file:

1. Right click on the import template file icon.
2. Select Properties from the menu.
3. View the size information on the General tab.

To reduce the file size and allow the template to complete the import process, please try one of these options:

• Save the import file as an XLSX file instead of a XLSM file.
• Save the import file as a CSV file instead of a XLSM or XLSX file.
• Create two (or more) import files and divide the data among each file.

Approximate Importing Time

The expected time needed to import forms is listed below. Please note, these times are the approximate import times; however, based upon the amount of data per form, your import may take slightly more or less time.

Import Codes for Kind of Payer

The following Kind of Employer codes should be entered in your Excel import file in place of the actual Kind of Employer terms.

Import Codes for Employment Type

The following Employment Type codes should be entered in your Excel import file in place of the specific employment type.