Automated spreadsheet markup project

Creating a spreadsheet markup project can save you time over manually redacting by applying markups to Excel spreadsheet and .csv files automatically. You can create a project by entering words, terms, phrases, or regular expressions that you would like Redact to apply markups to. Alternatively, you can enter project rules into a .csv file and upload it to the project page that will populate the rules for a spreadsheet markup project. Once the project is created and run, markups will be applied automatically based on the rules you create.

Supported file types

To learn more about which file types are compatible with Redact, see Redact supported file types.

Before you begin

Consider creating a saved search with the documents you wish to apply markups to and a markup set that contains the markups you wish to apply to the Excel and .csv files before starting the spreadsheet markup project creation process.

Considerations

• If you apply a markup to a cell where the text overflows into empty neighboring cells, just as in the native application, the markup does not overflow into empty neighboring cells. However, when the document is produced and the markup is burned in, the text from the original cell no longer overflows and is completely hidden by the markup.

• Due to a limitation in the Excel file format, a cell cannot contain more than 32,000 characters. If you try to apply a text redaction to a cell and it already contains 32,000 characters, the redaction may not be applied and you will see a notification about the character limit.

Creating a spreadsheet markup project manually

To create a spreadsheet markup project using rules that you manually set:

1. Navigate to the Redact Projects tab.
   

2. Click Create new project.

3. Select the Spreadsheet project option.

4. Complete the Create spreadsheet markup project section fields. To learn more, visit Fields below.

5. Complete the Rules section fields. This section is optional and if you complete these fields, they will determine how the spreadsheet markup project applies markups. To learn more, visit Fields. Alternatively, you can leave these fields blank and upload a .csv file. To learn more, visit Creating a spreadsheet markup project using .csv rules.

6. Click Save.

Creating a spreadsheet markup project using .csv or .xlsm rules

While a project can be created manually, if you plan on running multiple kinds of projects with similar rules and terms, you can save time by using the same copy of .csv or .xlsm rules for each project. Please note that you may still need to adjust the scope in the rules based on which project you are running.

You can upload up to 100,000 rules in a .csv file though it is worth noting that the more rules are included in a .csv or .xlsm file, the longer a project will take to run. Rules that are uploaded with a .csv file cannot be viewed in the Redact interface and instead, you will need to download a copy of the .csv file and view it outside of Relativity.

To download a copy of the spreadsheet rule template with dropdowns .xlsm file to your workstation, click here. Once you have entered the desired rules, save this file as a .csv before uploading to Relativity.

To download a copy of the spreadsheet rule template .csv file to your workstation, click here.

To create a spreadsheet markup project using rules generated by a .csv file, do the following:

1. Navigate to the Redact Projects tab.

2. Click Create new project .

3. Select the Spreadsheet project option.

4. Enter a Project Name and select a Saved Search and Markup Set. Leave all other fields blank. For more information on these fields, visit Fields below.

5. Click Save.

6. Click Upload rules csv.

7. Select the desired .csv or .xlsm file from your workstation and click Open.
   The file is uploaded and the rules will be created and added to the project.

Fields

The following sections and fields display while creating a spreadsheet project:

Create spreadsheet markup project section

The following fields display in this section:

• Project Name — enter the name for this new project.

• Saved Search — click on the drop-down menu and select the saved search that contains the Excel spreadsheet and .csv files you wish to apply markups to. Optionally, you can enter a term or terms into search box at the top of the menu to help narrow results and find the desired saved search.
  

• Markup Set — click on the drop-down menu and select the markup set that you wish to use to apply markups.

The following options work separately from the project rules. To learn more, visit Rules section. Depending on which of these options you select and what rules you include in a project, it is possible that one or more of these selected options will override at least some matches that the rules would otherwise find and apply markups to.

• Redact all headers and footers — select to replace the content in each header and footer with the default redaction text.

• Redact all Excel objects — select to apply a black redaction on all objects in the Excel documents included in this project. Excel objects include: WordArt, SmartArt, and embedded documents (email and Powerpoint), images equations, text-boxes, and shapes.

  Note: Selecting this option does not cause markups to be applied on charts. You can apply redactions to charts manually. To learn more, visit Manual markups with spreadsheet files.

• Redact all Comments — select and choose one of the following options to apply markups to comments in Excel documents:

  • Remove comments — select to remove any comments on spreadsheet files.

  • Remove comment text — select to replace comments with either [Redacted] or the text you enter in the box below this option.

• Fallback text—If you select redact options that are not valid and the project finds a match in a document, the text entered in this field will be used instead. For example, attempting to apply a black redaction to a .csv file is invalid and so the text entered in this field is applied as a redaction instead.

• Ignore certain rows or columns — select this option to prevent markups from being applied to specified rows or columns. This option can be used to ensure headers and footers display in Excel documents. Once this option is selected, the rows and columns to be excluded from markup section displays underneath. Enter the rows or columns separated by commas that should not have markups applied.

Rules section

The Rules section is optional and these fields determine how the spreadsheet project will apply markups once it is run. After completing the fields in this section, a new group of fields will appear below it so that you can create multiple rules in a spreadsheet project if desired.

Note: If you are uploading a .csv file, this section should be left blank. To learn more, visit Creating a spreadsheet markup project using .csv rules.

The following fields display in this section:

• Redaction/Highlight/Inverse/Inverse Highlight toggle—determines which type of markup you will be applying for this rule.

  Note: Highlights are not compatible with .csv files. If you select either highlight option and run a project, it will result in errors and markups will not be applied to terms that match this rule.
  

• Markup Reason — enter a description of why the markup for this rule will be applied to make it easy to track when reviewing markups using the Redact Navigation card.

• Markup Scope — determines the markup behavior when the project matches content in a document with a rule. Select one of the following options:

  • Character - places the markup on each cell where the entered characters match or on any non-cell character matches. This option can be used to redact information that is not a word such as an email handle or a social security number. Inverse redactions are not compatible with character markups. Markups applied using this option are not compatible with Convert Spreadsheet Markups.

  • Word — places the markup only on the exact matched text inside each cell that fits this rule. This option is only available if Redaction is selected.

  • Cell — places the markup on each cell that matches this rule. All of the content in the cell where a match is made will be covered by the markup.

  • Row — places the markup on the entire row where a cell matches this rule.

  • Column—places the markup on the entire column where a cell matches this rule.

  • Sheet — places a markup on the entire sheet where a cell matches this rule. If a match is found in the sheet name and not any other cells in the sheet, only the sheet name will receive a markup.

    • Include Sheet name & content — this option only displays when Sheet is selected. Select this option to apply a markup to both the sheet name and the sheet when a match is found. If this option is not selected and a match is found in the sheet name and not any other cells in the sheet, only the sheet name will receive a markup.

• Markup SubType — select the style of markup you would like to place for this rule. The options available in this drop-down menu are determined by the Redaction/Highlight/Inverse/Inverse Highlight toggle.

  Note: Certain fields and file formats only support Text redactions. If you select a Markup SubType other than Text, and the project applies markups to a location that only supports Text redactions, those redactions will be changed to show the project's specified Fallback text. Areas where this applies include:
  - All locations within a CSV
  - Excel sheet names
  - Excel headers & footers
  - Excel comments
  - Excel chart labels

• Place redactions on sheets without matches — select to place inverse redactions or highlights on any sheets that do not have content that matches this rule. This option best used when you want the majority of the documents to be redacted except for key information determined by this rule. This option can only be selected if Inverse or Inverse Highlight are selected above.

• Word/Phrase — enter the words, phrases, and text that you would like to apply a markup to for this rule. Multiple words or phrases can be added to a single rule group. The words, phrases, and text you enter are case sensitive.

  Note: This field does not support dtSearch or wildcard syntax.

• Name — enter a name for this rule. Optionally, click on the drop-down menu to view a list of commonly used regular expressions as well as custom regular expressions users have created and select one to populate both the Name and Regex fields. These commonly used regular expressions are a starting point and not intended be all-inclusive of every variation of these patterns. Variations in document type, text quality, and pattern variability should be considered when using regular expressions.

  

• Regex — enter a regular expression which can be used to identify important patterns like email addresses, social security numbers, credit card numbers, and any other content that may appear in a regular pattern throughout the documents in the selected saved search. Regular expressions require a name and the expression to be valid. After saving the spreadsheet markup project, the regular expressions you entered can be selected by name on other Redact automated projects within the same workspace. To see examples of commonly used regular expression terms, see Regular expression examples.

  Note: If you use regex101.com to help form regular expressions to enter in Redact, ensure that you set it to /gmi (global, multi-line, case insensitive) to help you better achieve the desired results in Relativity.

  When using regular expressions, the scope of markups applied is based on word. When a regular expression matches the word, it uses spaces to determine where to start and stop applying the markup. So if a set of words do not have spaces between them, a markup will be applied to the entire set and not just the word that matches your entered terms.

  The following table includes potential terms that can be searched using regular expressions and an example of a word or phrase that will be completely covered by a markup to illustrate this behavior:

  Regular Expression Terms	Marked up content
  jane, smith, relativity	jane.smith@relativity.com
  police, woman	policewoman
  mother, in, law	mother-in-law

• DtSearch - Enter a DtSearch syntax where you would like to apply markups to any matches based on the Markup Scope and Markup SubType options selected. By default, Redact will apply markups to every term in a search syntax. DtSearch uses cross-cell matching so if a rule has text that would span multiple cells, all cells that contain a match will be redacted.
  Optionally, you can apply markups to part of a DtSearch syntax. For example, apple w/2 pear causes both apple and pear to receive markups. If you would like to apply a markup to one of the terms, in this case, apple, use the following syntax: (?<redact>apple) w/2 pear.

  Noise words and the alphabet list are not compatible with this field. We recommend using W/N operator for proximity matches instead.

  Note: Combining special characters or operators may lead to inaccurate results. We recommend using Regex in these situations instead.

  The following syntax options are available with this field:

  Special characters or operators	DtSearch functionality
  AND, OR, NOT	Boolean operators
  ?,*	Wildcards
  W/N (or WI)	W/N operators
  PRE	Proximity with terms order
  xfirstword, xlastword	Built-in search words
  ()	Operator precedence
  ""	Search words that are operators
  %	Fuzzy searching
  ~	Stemming
  (<?redact> {term})	Partial redaction
  !"#$&'()*+,./:;<=>?@{|}^{|}~˜	Special characters recognized as spaces that cause word breaks
  #	Phonic searching
  =	Numerical patterns

Running the project

Once you have created a project, you can run it to apply markups. To learn more, visit Running and reverting a project.

Reviewing markups

After markups have been placed, it is a best practice to perform quality control on documents before they are produced. To learn more about how to do this using Redact, visit Reviewing markups to ensure accuracy.