5 methods and tools to make Excel work more efficient! Also explains the work of functions and automation candidates

*This is a shortcut key for Windows terminals. If you have a keyboard without a numeric keypad, the keys you press may be different.

By using these shortcut keys, you can quickly operate Excel. If you master it, you will be able to make your work more efficient.

use functions

You can also make your work more efficient by using Excel functions*.

*A function in Excel is a predefined formula. You can calculate total values, etc.

The table below lists the main Excel functions.

These functions can be used for a variety of tasks, from simple calculations to complex data processing. By leveraging functions, you can save a lot of time on manual calculations and data processing. Functions are also flexible and can easily adapt to changes in data.

However, as of February 16, 2024, there are hundreds of Excel functions. It may be difficult to remember everything at once. It is a good idea to start by learning and using functions that may be used frequently, such as SUM and AVERAGE.

Details of some functions, such as SUM and AVERAGE, will be described later.

use macros

You can also make your Excel work more efficient by using macros*.

*Macros in Excel are functions that automate repetitive tasks within Excel. When using macros, we use a programming language called VBA. The details of VBA will be explained later.

For example, you can automate the creation of regular daily, weekly, and monthly reports. Depending on the programming content, data import, aggregation, analysis, chart creation, formatting, report conversion to PDF, email sending, etc. can be automatically executed as a series of processes.

Macros are also effective for processing large amounts of data. They can significantly reduce the time required to perform specific operations or calculations on large data sets. They can speed up data analysis and aggregation. The time saved can be used to focus on your core business and be more productive.

However, be careful when using macros. There are also viruses that exploit macros. If your computer is infected with a virus, your personal information may be stolen or your files may be destroyed. Use only trusted macros and avoid running unknown macros.

Use the shared folder function

We also recommend using the shared folder function*.

*The shared folder function is a folder on a network where Excel files are placed so that multiple users can access and edit them.

It's especially useful when teams or project members work together, making it easy to share files. People in different locations and time zones can access the same data and documents and make changes instantly.

In addition, the use of shared folders allows for centralized data management. This makes it easy to organize files and manage versions. At the same time, access rights can be managed through security settings, allowing you to control access to information.

Use RPA tools

RPA* (Robotic Process Automation) tools are extremely useful when making Excel operations more efficient.

*RPA is a technology that uses software robots and AI to automate routine and repetitive business processes.

RPA tools primarily focus on automating simple tasks, and by minimizing human intervention, they can improve operational efficiency, improve accuracy, and reduce costs.

For example, by linking Excel and RPA, the following can be achieved.

Reference source: Ministry of Internal Affairs and Communications | RPA implementation guidebook in local governments | Page 19 (as of February 16, 2024)

If you are spending time on data collection tasks, RPA can be of great help. Also, the above is just one example. Depending on how you use it, you may be able to streamline or automate other tasks.

Explaining functions that are useful for streamlining Excel work

Here, we will explain functions that are useful for improving the efficiency of Excel operations.

• SUM
• AVERAGE
• VLOOKUP
• MAX・MIN
• SUMIF/SUMIFS
• COUNTIF/COUNTIFS

SUM

The SUM function is used to calculate the sum of numbers in a range of cells.By using the SUM function, you can save yourself the trouble of manually summing large amounts of numerical data. Even if your numerical data is spread across multiple cells, you can easily and quickly get the total value using the SUM function.

The SUM function can also sum multiple cell ranges at the same time, making it suitable for aggregating and aggregating data. This allows you to quickly extract the necessary information from large amounts of data.

Additionally, the SUM function can be used in conjunction with other functions. For example, you can easily calculate the sum of data that meets a certain condition by combining it with the SUMIF function.

AVERAGE

The AVERAGE function is used to calculate the average value of numbers in a range of cells.For example, if you have a column that contains numeric data such as budget or sales, you can easily find out the average value by using the AVERAGE function to calculate the average value for that column. By knowing the central value, you may be able to obtain useful suggestions for management from data dispersion and extreme values.

The AVERAGE function can also be used in conjunction with filtering and sorting to help calculate the average value of data based on specific criteria. This allows you to quickly obtain averages for different segments and categories, and the data obtained may be used to build and improve strategies.

VLOOKUP

The VLOOKUP function is a function that searches data vertically and retrieves matching values. The V at the beginning stands for Vertical, and the VLOOKUP function allows you to quickly find matching rows even in large datasets. For example, searching for a product name or price by entering a product code is faster and more efficient than searching manually.

Additionally, the VLOOKUP function can flexibly respond to data updates and changes. Even if new information is added or existing information is changed, you need to be careful when adding or deleting information at the beginning or end of a line, but this can be handled by carefully adjusting the read range of the function. . This will allow you to respond more flexibly to data changes, which will improve your work efficiency.

MAX・MIN

The MAX function is a function that retrieves the largest value in a specified range.The MAX function allows you to extract the maximum value from a dataset. For example, this is useful when finding the highest sales amount from sales data. Even if the range of data changes, the MAX function automatically retrieves the largest value within that range.

Conversely, the MIN function retrieves the smallest value from a data set.For example, it can be used to find the lowest stock quantity from inventory data. Even if the value is changed, the MIN function will get the minimum value after the change.

These functions go beyond simple calculations to perform complex conditional data extraction and analysis. For example, it is possible to extract the maximum and minimum values ​​from data that meet certain conditions. By using the MAX and MIN functions, you can expand the range of data summarization and analysis, and sometimes make it possible to make quick decisions.

SUMIF/SUMIFS

The SUMIF function calculates the sum of cells in a range that meet a single condition.For example, this can be useful when you want to total data based on a single criterion, such as the total sales of a particular product or the total by region.

The SUMIFS function also calculates sums within a selection based on multiple criteria.For example, you might want to calculate sales for a specific product category over a specific time period. When multiple conditions are required, SUMIFS gives you more flexibility in filtering the data and calculating the total.

COUNTIF/COUNTIFS

The COUNTIF function counts the number of cells in a selection that match one condition.For example, it is useful for counting the sales volume of a specific product from sales data, or counting the number of transactions within a specific period.

The COUNTIFS function also counts the number of cells in a selection based on multiple criteria.For example, you can count the number of cells whose data corresponds to a particular region and product category.

These functions are useful for data extraction and analysis to quickly determine how much data meets certain criteria.

VBA and more! Languages ​​that help improve work efficiency

Here, we will explain programming languages ​​that will help you streamline your work.

• VBA
• Python

VBA

VBA (Visual Basic for Applications) is a programming language that can extend the functionality of applications such as Excel, automate repetitive tasks, and perform complex calculations.Use this VBA when using the macro mentioned above.

By using VBA, you can streamline or automate tasks such as data formatting, filtering, graph creation, and report generation. However, VBA is merely a programming language that extends functions. Please note that you cannot freely develop programs from scratch.

Python

Python is a highly versatile programming language. It has the characteristic that you can write clear and logical code with a relatively small number of lines of code, making it easy for beginners to use.

Python can actually be used with Excel. By using "Python in Excel", you can perform Python processing within Excel. You can perform data aggregation, graph creation, machine learning, collaboration with existing Excel functions, etc. Python will be useful when you want to streamline and automate such tasks.

Operations that should be reviewed and automated to improve efficiency

Here we will explain the tasks that should be reviewed and automated.

• Work that does not change productivity
• Work with a set work content

Work that does not change productivity

Tasks that need to be reviewed or automated to improve efficiency include tasks that do not result in any change in productivity.Even if productivity remains the same when done manually, automation can greatly improve productivity.

For example, when entering data manually, no matter how hard you try, there is a limit to the number of entries you can enter in an hour. Even if you concentrate on your work, it will be difficult to double the number of entries at once. Even if you can do it, if you work for a long time, you will get tired and your work speed will gradually slow down. If you look at the entire day rather than the hour, your productivity probably won't improve that much. Even if it were improved, it would be limited to 2-3 times.

However, if you automate your work, you can expect a significant increase in productivity. This is because it inputs data at a speed that would be unimaginable if done manually. Depending on the data input content and tools, it may be able to execute tens to hundreds of times faster.

Best of all, when automated, it can run 24 hours a day, 365 days a year. They don't get tired like humans and perform tasks at the same pace. If you think about it on a yearly basis, you will be able to significantly improve your productivity.

Work with a set work content

Even tasks with fixed work content can be considered tasks that should be reviewed and automated.This is because it is easy to manualize or automate.

For example, the following are main examples of tasks where the work content is fixed.

• Posting
• Employee attendance management

In the case of transcription, the basic procedure is to "transcribe or copy and paste the contents written in A into B." What needs to be done is clear, and the business process is very simple. It will be easy to program. Also, by automating it, you may be able to reduce input errors that occur when doing it manually.

When it comes to employee attendance management, automation allows you to quickly record attendance and attendance, process vacation requests, and calculate overtime hours. As a result, the burden on administrators will be significantly reduced. This is especially useful for large companies with many employees. Regarding attendance management, the work content is expected to be simple, so the programming may not be complicated. If possible, it is better to automate it.

Frequently asked questions about improving the efficiency of Excel operations

Here, we will explain frequently asked questions regarding improving the efficiency of Excel operations.

• Please tell me a successful example of improving efficiency in Excel operations.
• What is DX?

Please tell us about successful examples of improving efficiency in Excel operations.Material change

The following are examples of success stories for improving efficiency in Excel operations.

Enter the information necessary for searching and inputting, such as address number and office number, into an Excel file from documents such as notification forms and tax returns related to personal residence tax. RPA reads the contents of the Excel file line by line, searches for the address on the core system, moves to the input screen, and then transfers and registers the items from the Excel file. In addition to the special collection transfer notification and summary form (business address) that were originally planned to be introduced with RPA, it is now possible for employees to input a wide range of materials. Regarding the summary table (office address), we revised the business flow so that the eLTAX data was converted using an Excel macro and then entered using RPA. In order to streamline the process of confirming that the registered contents of some materials are correct, we have also automated the process of processing information in core systems to make it easier to view. In addition, since it is difficult to allocate manpower to scenario creation during the initial tax period, which is the busiest time in the Municipal Tax Division, RPA was introduced this time mainly for monthly processing. By streamlining regularly occurring monthly processing, we are able to alleviate the workload during busy periods.

Source: Ministry of Internal Affairs and Communications | RPA implementation guidebook in local governments | Page 67 (as of February 16, 2024)

This case study aims to improve efficiency by combining Excel macros and RPA in the business process related to personal residence tax.

First, we entered the necessary information (address number, business office number, etc.) from materials related to personal residence tax into Excel, and based on that data, we transcribed and registered the searches and registrations in the core system. We also reviewed the processing methods for special collection change notifications and summary sheets (business address), converted eLTAX (electronic tax return system) data using macros, and improved the flow to automatically input data using RPA. This has helped reduce the workload during busy periods.

Especially in the case of local governments, it is expected that tens of thousands of pieces of data will be input and processed. Therefore, it can be said that it is a big deal that we were able to streamline this type of work.

What is DX?

DX (Digital Transformation) refers to the use of digital technology (IT) to transform a company's business model, organization, processes, and culture in order to establish a competitive advantage.DX refers to the process of not only improving business efficiency but also creating new value by utilizing digital technology.

To give specific examples, the following are mainly referred to as DX.