loading

Common Errors and Tips/Tricks

What You’ll Learn

In this section, you will learn:

  • Commonly seen errors
  • Tips and tricks

Commonly seen errors

Hiding rows or columns, instead of deleting

If you want to hide a row/column for a specific reason, there’s no issue (just be sure to unhide when you’re ready to send the file or use the file for upload). However, be sure to delete rows/columns if you no longer want them included in your data set, instead of hiding the row/column from view. If the recipient of the spreadsheet auto-fits the data set (or unhides the rows/columns), they’ll see these hidden rows/columns appear and the data will not match what you intended to send.

Applying extensive formatting/styling

You can apply a background color and bold each header value in a data set, if you’d like (however, this is purely aesthetic). You might also highlight potential duplicates or invalid data points in a provided file (this is functional). However, you should be careful not to add extensive formatting/styling to a spreadsheet. If the formatting is not adding value (such as the header background), it’s recommended that you don’t include. There are many instances where colors will start to clash once you and multiple others have edited the spreadsheet which causes confusion and could lead to an error.

Applying a filter to a subset of columns

A filter is needed on all columns in your data set to ensure data integrity is kept. If you only apply a filter to a subset of columns, the data in the columns without the filter will not be updated with the rest of the data set. For example, if you apply a filter to the first two columns in a data set and not the third column, sorting the first column by descending would result in the second column’s associated data moving in conjunction. But, the third column’s data would remain the same - see below:

Filter Filter No Filter
1 4 1
2 5 2
3 6 3

If we then sorted the first column by descending, the result would look like this:

Filter Filter No Filter
3 6 1
2 5 2
1 4 3

You can see that the third column’s values did not change places with the rest of the data set and are now incorrectly associated with the values in the first two columns. The shortcut for applying (and removing) a filter is Alt + A + T and the shortcuts for sorting are Alt + A + S + A (ascending) and Alt + A + S + D (descending). You can input a text value to filter on with Alt + E and can then clear that filter with Alt + C (or Alt + A + C to clear all filters across the data set).

Pasting to a filtered data set

You should not paste a series of values to a filtered data set as Paste will not respect the hidden values. See the example below:

Filter Filter Filter
1 4 7
2 5 8
3 6 9

If you filtered the first column to only 1 and 3, you would see the following on your screen:

Filter Filter Filter
1 4 7
3 6 9

However, if you pasted two rows of data (such as 100 and 200) into the top left cell, you’d see the following result once unfiltered:

Filter Filter Filter
100 4 7
200 5 8
3 6 9

You can see that 3 is unchanged because it’s in the third row and only two rows were pasted - an applied filter is not respected by Paste (instead, the hidden value 2 was overwritten).

Tips and tricks

Using Find and Replace with Text to Columns

You can use find and replace to add a common delimeter to a series of symbols or characters. For this example, imagine you wanted to break out the contents of a column using Text to Columns, but wanted to do so using “Yext” as a delimiter (i.e. data is formatted as Yext123Yext456Yext789 and you want Yext123 in Column A, Yext456 in Column B, and Yext789 in Column C). You could input “y” as the delimiter, but it’s possible that there are other instances of “y” in the column that you don’t want to use as a delimeter (and you’d also need to find “ext” and replace with “Yext” after separating the data) which makes this a nonviable strategy. Instead, you could find “Yext” and replace with “^Yext” (choose a character that does not exist in the column). You could then run Text to Columns by inputting ^ as the delimiter.

Using Paste Special to Transpose

If you have a list of values stored as a row that you want stored as a column (or vice versa), you can use Paste Special and select Transpose. If the data you selected was stored in a row, it will be pasted as a column.

Selecting Multiple Cells to Take Action On

You can select multiple cells at a time (that are not next to one another) by holding Ctrl and selecting each cell. You can then take an action in the final cell that is applied to all selected cells.

unit Quiz
+20 points
    Error Success Question 1 of 2

    If you do not need a row of data in your data set anymore, what should you do?

    Error Success Question 2 of 2

    What is the potential issue with pasting to a filtered data set?

    You're a star! ⭐️

    You've already completed this quiz, so you can't earn more points.You completed this quiz in 1 attempt and earned 0 points! Feel free to review your answers and move on when you're ready.
1st attempt
0 incorrect