Report Tweaks in Excel

11 November 2021

TECH TALK SESSION

Our users know that Accudemia reports provide data in a wide variety of ways, with minimal need for processing once the report is downloaded. Sometimes, though, it can help to be able to process some of that data once it’s exported into Excel. There are also many ways you can use Excel to help with the files you import and export from Accudemia. In this week’s Accudemia Tech Talk segment, Support Specialist Jorge Rodriguez gave some tips and tricks for using Excel to help your Accudemia data go further.

Filtering Information Based on Specific Data
Accudemia reports can be filtered before you run them, but sometimes, you want to take a look at all the data before you filter it. That’s why it’s handy to know how to filter spreadsheets in Excel if you pull an unfiltered report or export a large amount of data from Accudemia. By using this method, you can isolate specific items in a data file such as students, courses, or services. This allows you to see, check, or fix issues like missing unique fields, or incorrect formats for your data. For example, filtering data for your courses may reveal the names of your courses aren’t standardized the way they should be.

Searching and Replacing Information Based on Specific Criteria
Using the Search and Replace function in Excel, you can find specific lines of data, and if you need to, replace them en masse with different text. This is especially helpful if you’re building an import file for a new term that will contain much of the same data as the file from the previous terms. For example, if you need to change the term name in the appropriate column, this is a method that will only take a couple of seconds!

 

Modifying/Concatenating Data
This is a good function to know about in Excel because it allows you to combine data from two or more columns. As you can see in the example image, one use of this function is to combine the course name and section number, making it easier to understand which section’s data you’re looking at in a given row.

VLOOKUP to Search/Merge Data From Different Sources
This method allows you to use or connect available information from one source with another source. As an example, we’ve seen admins use this process to combine export data from Accudemia with the Attendance by Service report to include the email column, which is in the user export data, but not the Attendance by Service report. While our development team is always looking at what users are doing and asking for feedback to determine which reports would be beneficial in Accudemia next, if you can’t find exactly what you need, the VLOOKUP process can help.

Creating Subtotals
Sometimes, it’s not the data that needs adjusting, but how it’s displayed or quantified. Excel has tools to get subtotals of information that can help you further drill into your data. For example, if you have a list of the total number of visits to your center organized by service, you can use subtotals to see how many visits you have for each specific service.

Analyzing Data
Excel has a built-in analyzer tool that allows you to ask a question about your data. For example, you can ask which Service was utilized the most, what hours are most popular, or which students visited you the most.

Power Query
Power Query allows you to gather and transform data in Excel. As an example, you could pull a list of students who came to your center the previous week and who you want to contact using your email set up in Outlook. With this functionality, you can use the student data to create a mail merge document in Word which then will be used by Outlook to send the email.

Join us next time!
Engineerica Systems is pleased to present our Tech Tip segment recap for 11/9/2021 for the weekly Accudemia sessions. Held every Tuesday at 1 pm EST, Accudemia clients can join a session to learn about a quick, useful topic from one of our Support Specialists. Each week, we’ll follow up Tech Tip Tuesday with a blog recapping that week’s topic. If you’re an Accudemia client and would like to join us for the next session, just visit
https://www.engineerica.com/accudemia-weekly-sessions/.