Excel樹林– category –

Environment OS: Windows 10 Pro (Version: 20H2) Excel Version: 2108 Background When creating a calendar in Excel, I wanted to configure settings to display or hide the date "31" depending on the month. Specifically, I wanted to create a c...

Environment OS: Windows 10 Pro (Version: 20H2) Software: Microsoft Excel Background While working in Excel, I wanted to display a cell's value embedded within a text string. For example, I wanted to display text combined with cell data, ...

Environment OS: Windows 10 Pro (Version: 20H2) Software: Microsoft Excel (Version: 2108) Background I was struggling with an issue where the print size and layout changed when converting Excel data to PDF. I could not save the document e...

Introduction When you frequently enter numbers with decimal points (e.g., 14.3, 9.8, 3.4) in Excel, it can be surprisingly stressful if "the decimal key is hard to press" or if "you don't have a numeric keypad." I actually handle such ta...

Introduction In Excel, there are many situations where you want to automatically show a date when text is entered into a certain cell. I recently had a request to display the current date in column B whenever a name or data was entered i...

Introduction When using Excel, you often encounter situations where you want to extract specific parts from a text string separated by spaces or symbols. I myself wanted to "separate and obtain the last name and first name from a string ...

Introduction When using Power Query to shape data in Excel, have you ever noticed the item "Other Queries" displayed in the sidebar of the Query Editor? For those unfamiliar with it, you might wonder, "What is this?" In this article, I w...

Introduction When processing data using Power Query, you often encounter situations where you want to "extract only a specific part of a string and display it in a new column." The data I faced contained strings like the following: Measu...

Background While using the Power Query function in Excel, I encountered the error: "[Expression.Error] The column 'Column1' of the table was not found." In this article, I will introduce how to handle this error. Causes and Solutions The...

Introduction Using Power Query, you can batch import, transform, and aggregate multiple files located in the same folder. In this article, I have summarized the steps on how to actually specify a folder as a data source in Power Query ba...

Introduction Using Excel's Power Query feature allows you to efficiently import and transform only the necessary rows from a CSV file. In this article, I will summarize the actual operational steps for individually extracting and shaping...

Introduction When creating a chart in Excel, have you ever encountered a situation where the dates on the X-axis (e.g., "2023/12/14") are displayed horizontally, causing them to overlap or take up too much space? In this article, I will ...

Introduction I wanted to use Excel's Power Query function to calculate the average of time data and add it as a column, so I learned and implemented the method. This technique is particularly effective when you want to process data in a ...

Introduction When importing data using Power Query, have you ever encountered a situation where the cell values are not recognized as the intended data type? For example, numbers or dates might not be recognized correctly, preventing cal...

Introduction When importing data using Power Query in Excel, do you ever feel that clicking the [Refresh All] button in the "Data" tab every time is a hassle? In this article, I will show you how to improve work efficiency by using VBA t...

Introduction When using Power Query in Excel, you may encounter the unfamiliar term "M Language" (or M Script). At first, I was confused and didn't know what it meant, but after researching, I realized it is a crucial mechanism that supp...

Introduction While shaping and processing data in Excel's Power Query, you may encounter situations where you want to retrieve the minimum value contained within a specific column. In this article, I will share the procedure for calculat...

Introduction When creating large, database-like tables in Excel, have you ever wondered, "How far do these columns actually go?" I investigated the column limits when I needed to handle a massive amount of data in Excel, so I will share ...

Overview I have previously used Excel functions like =RAND() and =RANK() to generate random values and process rankings. However, as I move towards improving business efficiency and automating processes, I wanted to reproduce similar pro...

Background When handling work hours or durations in Excel, there are often times when you want to convert "Time" into a decimal number. For example, expressing "8 hours and 30 minutes" as "8.5" makes calculations much easier. Currently, ...

Background When working in Excel, you often want to aggregate data that is split across multiple sheets. In my case, I had raw data in Sheet2 and wanted to summarize it in Sheet1. However, counting manually how many times a specific text...

Introduction When importing data using Power Query in Excel, you will often see "Transform Sample File" and "Other Queries" displayed in the sidebar of the Query Editor. While both seem to involve data transformation, their purposes and ...

Background I wanted to restrict the values entered into cells in Excel to dates only (yyyy/mm/dd), so I looked into how to do it. Steps Here is the procedure: Select the target cells. Click the "Data" tab on the menu bar. Click "Data Val...

Background When I tried to calculate the average of a range in Excel, I couldn't get a result because one of the cells contained a #DIV/0! error. I studied how to calculate the average while ignoring this #DIV/0! error, and I will share ...

Introduction Have you ever opened an Excel file and found that the sheet tabs usually located at the bottom of the window have completely disappeared? Without them, you cannot switch between sheets, which is very inconvenient. In this ar...

Introduction In this article, I will introduce how to automatically change the color of cells containing specific text in Excel. For example, if you want to change the background color of cells containing the word "Forest" (森), you can ...

Introduction When working in Excel, you may encounter the following message every time you save a file: "Be careful, parts of your document may include personal information that can't be removed by the Document Inspector." If this messag...

Introduction When using Excel, there are often times when you want to restrict the values that can be entered into a cell. "Data Validation" is the go-to feature for this. For example, you might want to set up a list in a cell so users c...

Introduction When plotting data on a graph, have you ever found yourself manually placing a shape on a dotted line, wondering, "Where exactly does this value fall?" I used to manually paste red dots while looking at the numbers, but this...

Introduction In Excel, you may want to display units like square meters ("m2") with the "2" showing as a small number at the top right, like "m²". Here is how to format the text to achieve this appearance. Steps Here is the procedure: Op...