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:
If we then sorted the first column by descending, the result would look like this:
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:
If you filtered the first column to only 1 and 3, you would see the following on your screen:
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:
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.