The Boring Lab

Excel Tricks to Speed Up Video Security Management

Learn how you can use Excel to automate your Milestone XProtect VMS. Build reports in minutes, not hours. Trust us, it's a game changer.
Table of Contents

As XProtect Administrators, we spend quite a bit of our time in excel creating and updating spreadsheets. Perhaps, too much time.

And I don’t know about you but I wholeheartedly believe that if we can figure out how to get to space and cure life-threatening diseases, I definitely should not feel like I’m stuck in the stone ages updating one cell at a time.

We’re living in the future people!

four men dressed in flinstones costumes riding in a DIY foot powered caveman vehicle

We’ve got to seize the day and all of the excel tips so we can get back to what really matters: not sitting in front of our computers all day.

These are some of the best Excel tips to help you speed up some of those tedious XProtect management tasks. Now go forth and knock out those monthly inventory reports, manage your footage archives, and “yabba dabba do” it all before 5:00pm.

Common keyboard shortcuts

Using keyboard shortcuts in Excel can help speed-up data input up without having to touch the mouse. Pebbles and Bam Bam approved.

Try some of the most common ones:

Ctrl + A

Use Ctrl + A to highlight all of the data in the spreadsheet.

Use Ctrl + C to copy data or cells.

Use Ctrl + V to paste data or cells.

Use Alt + Shift + Right arrow to group rows or columns.

Use Ctrl + 9 to hide an entire row.

Use Ctrl + 0 to hide an entire column.

How to remove duplicates

To remove duplicates in Excel, try this shortcut:

  1. Highlight the column or dataset and use the shortcut “ALT + A + M.” This will open the “Remove Duplicates” drop-down menu with your column headers selected.

  2. Select the column you want to remove duplicates from and click the “OK.”

  3. Every duplicate in the selected column or dataset will be removed.

How to delete all empty rows at once

Cleaning data and deleting empty rows one by one is enough to drive any caveman mad.

Here’s how to use Excel’s filter function to delete them all in seconds:

  1. Click F5, then click “Special.”

  2. In the “Special” menu, select “Blanks.”

  3. Click “OK.”

  4. This will highlight all blank rows in your worksheet

  5. Press Alt + type “EDR”

This will delete all of the empty rows in your Excel spreadsheet. Dare we say, it’s the next best thing since the invention of the wheel.

How to convert a PDF to Excel

PDFs are a great way to share documents, but they can be soooo annoying to edit. Modern day tip: convert them into Excel tables and BAM! Problem solved.
@cheatsheets How to convert any PDF to Excel easily #excel #pdf #tutorial ♬ Sunny Day - Ted Fresco

Here’s how:

  1. From the Data tab, click ”Get Data”

  2. From there, select “From File” and “From PDF”

  3. Select the PDF you want to convert and click “Import”

  4. A window will appear where you can select the data table you would like to import from the PDF to your worksheet.

  5. Click Load

  6. Excel will open with the PDF data in a new Excel tab.

Now you can edit the data as you would any other Excel table.

How to generate insights using AI

In this post-prehistoric world, Excel has upped their data analysis game. Did you know that you can now use artificial intelligence (AI) to analyze data and find trends?! This is a game changer for XProtect reports.

It’s as simple as 1, 2, 3…

  1. On the home tab click “Analyze Data.”

  2. Using AI, Excel automatically creates pivot charts, pivot tables, and generates insights.

  3. From there, click “Insert Chart” for the chart of your choosing.

How to create drop down lists

Create your own Excel drop-down lists to make data input infinitely easier.

To create an Excel drop-down list:

  1. Select the cell where you want the drop-down list to appear.

  2. Go to the Data tab and select “Data Validation.” (Or press ALT + A + V + Vas a shortcut)

  3. In the new window, select List from the Allow drop-down menu.

  4. Click on the button next to Source data and select the range of cells that contain the items you want in your drop-down list.
Now you can choose prefilled options from the drop-down list to make inputting data easier!

It’s clear we’ve moved past the Stone Ages of Excel, and no one wants to see a security admin in a loin cloth anyway.

The truth is, Excel is great for automating some of the tedious and time-consuming tasks involved in managing Milestone XProtect.

We’ve looked at ways to import data automatically, format data for reporting, and create charts and graphs to help you visualize your data.

With Excel’s many built-in features and the wealth of online resources available, there’s no end to the ways you can use it to make your job easier.

Team Boring

Your go-to XProtect eXPerts. We learn the technical stuff that will save you time and make it less boring.

Team Boring

Your go-to XProtect eXPerts. We learn the technical stuff that will save you time and make it less boring.

Love learning about all things XProtect?

Sign up for our [NOT so] Boring Newsletter to get a monthly dose of XProtect tips, security trends, and a touch of humor delivered directly to your inbox.

You Might Also Enjoy…