Effective utilization of Excel resources with smart sheets
Part One
first
In many Japanese companies and organizations, Excel is central to the recording and processing of business information.
In this tutorial, we will take an Excel sheet of equipment management as an example and explain how it can be used smartly by importing it into a smart sheet and setting it.
import
How to import
To import, click the plus part in the menu on the left to display the Solution Center. Click Import from there.
In addition to Excel, there are other types of files that can be imported, such as Google Sheets, but here, select Excel and select the file you want to import.
Import settings
When importing, the screen called "Import Settings" will appear, so select the column header and primary column here.
Primary column
The primary column has a meaning similar to the title of each row.In the Japanese localization of smart sheets, the note in the import settings says, "The primary column must contain the" main descriptor "of each row."
It's a bit confusing, so I'll explain how the primary column is used as a real function.
First, it becomes the reference column in the "indent" that structures the row on the tree.Also, in "Card View", the information of the primary column is displayed as the title in the minimum display.
Row with column items
Then click the row that contains the column header as the column header.
In Japanese organizations, when using Excel, "table title" or "creation date" is often displayed at the top, but those rows are ignored and the tabular data is displayed. Select the row with the item names in the first row of the "table part" that is lined up as the "column header".
Excel sheet pretreatment
In addition, this is also often used in Excel in Japan, but if there is "cell merge", import will not work, so as a preliminary process, "cell merge" is canceled and information is lined up in "table format". Must be in a state.
Implementation of import
Click Import when you're done.
The screen immediately after importing has a shape very similar to the Excel sheet.
Setting column properties
What are column properties?
Smartsheets use "data types" that are often used in databases such as MySQL to improve data integrity and make them more convenient to use.
Column type
Column types include String / Number, Contact List, Date, and Drop-Down List.
Here, we will explain the column types used in the "equipment management table".
String / number
This column type can be a string, a number, or a combination of the two.
For example, as a character string, you can put a slightly longer comment in the "Remarks" section as a character string.The character string can be displayed in multiple lines by setting it to wrap display.
For numerical values, for example, the "Current number" and "Usage rate" columns correspond to this.By formatting the numbers, you can display them in currencies or percentages.
It is also possible to enter an "expression", but the "expression" will be explained separately.
Contact list
The "Contact List" column type is used to connect tasks in rows to people.
For example, "assignee", "approver", and so on.
In this sheet, "Ordering person", "Administrator", and "Contact person mail" of the supply destination are set in the contact list.
You can only enter contact names and email addresses, names only, or email addresses.
In the case of an account such as Google, a face photo will be displayed if a photo is set for that account.You can also set it in "Personal settings" of the smart sheet.
Date
The "Date" column type is used to connect tasks in a row to some date and time.
For example, "start", "end", etc. of a task correspond to this.
In this sheet, set "Purchase / Lease Date" and "Today" as dates.
A date selection calendar is displayed in the cell, making it easy to select a date.
Drop-down list (single selection)
Selecting the drop-down list (single selection) column type allows you to enter the values to display in the list.
Since the rows are categorized by the values in the list, you can create columns for the cards based on that information in the card view. (I will explain in the next item.)
Enter the values in the drop-down list one per line.
In this sheet, set "Category", "Storage location", and "Company name" in the drop-down list.
View of Smartsheet
The smart sheet can be displayed in "Gantt view", "Card view", and "Calendar view" in addition to the standard "Grid view" like an Excel table.
Display in card view
Click on the "Grid View" section of the menu and select "Card View".
The essence of the row information is displayed based on the display method of the column set as "Drop-down list (single selection)" in the column property setting earlier.
Lane change due to change in display method
If you change the display method to "Storage Location", the columns of cards will be displayed based on "Storage Location".
Compact display
If you select the one with short cards lined up in the upper right menu, it will be "compact display" and only the "equipment name" card set as the primary column will be displayed.
Card view settings in full view
If you click "Card View Settings" like the gear on the upper right, you can select up to XNUMX fields to be displayed in "Full View" in addition to the "Title" specified in the primary column.
If you've used Trello before, setting up Card View may seem similar to editing Trello tiles.
Here, select "Current number" and "Usage rate" from the purpose of use.
Pasting an image into a cell
Smart sheet sheets allow you to insert images directly into the sheet's cells.
How to insert an image in a cell
Make the view a grid view and select the cell where you want to place the image.
Click the Insert Image button on the toolbar.
Select the location of the image and click Open.
The image is scaled down by the width of the column, and expanding the column also enlarges the image.
Paste the image on other lines as well.
Checking the image in the card view
After pasting the image in each row in the cell as described above, if you move to "Full View" in the card view, the image will be displayed on the card as shown below.
Display in Gantt view
What is Gantt View?
The Gantt view is one of four types of views on the smart sheet, typically used in project management to keep track of when each task starts and ends, how it's done, and how it's progressing. ..
WBS (Work Breakdown Structure)
In the Gantt chart, the relationship between work content, person in charge, start date, end date, and work is based on WBS (Work Breakdown Structure), which summarizes each process of the project in a tree structure up to the work level of each person in charge. And so on.
Timeline
In the horizontal direction, a timeline such as date is placed on the horizontal axis, and a "bar" like a horizontal bar visually indicates the period and progress.
Areas of use of Gantt charts
Gantt charts are almost always a tool used in traditional waterfall project management.It can also be used to make the relationship between schedules and tasks easier to understand visually.
By the way, the "Gantt" in the Gantt view or Gantt chart comes from the fact that the inventor is a mechanical engineer and a management consultant "Henry Gantt".
A little atypical usage
The Gantt view of equipment management has a slightly atypical usage.The bar on the horizontal bar indicates the period from the date of purchase to today.It is also assumed that the "quantitative ordering method" is used to replenish equipment when the remaining inventory falls below a certain number of "ordering points".Then, taking advantage of the fact that the percentage can be displayed in the bar in the Gantt view of Smartsheet, the "usage rate" obtained by dividing the current number by the order point is displayed in the bar.
This allows you to visually see how long each piece of equipment has been purchased, and what percentage of it is up to the next replenishment point.
To use Gantt view, the sheet needs at least two date columns, a start date and an end date, but here we use two date columns, a purchase / lease date and today.
Also, in order to automatically calculate today and the inventory rate, set the formula of the smart sheet that corresponds to the formula of Excel.
First, set "= order point @row / current number @row" for the usage rate, select the column, and display the column data in "percentage format" from the menu above.
Next, "Today" should be automatically entered by the formula "= TODAY ()".
Call "Project Settings" from the gear-like menu in the upper right of the Gantt view, select "Start Column" and "End Date Column" in "Date Range Display" from the Dependency Settings menu, and select "%" in "Options". Set the "Completion column" respectively.
Conditional format
Conditional Formatting updates the formatting of individual cells or entire rows when certain conditions are met.
Also, in Gantt view and Card view, the color of the bar etc. is updated.
Here, let's write the condition so that the color of the bar of Gantt view is set according to the classification of equipment.
The finished image looks like this.
Gantt view
Card view
Definition of conditional formatting rules
Click Conditional Formatting on the toolbar to display the Conditional Formatting window.
Click Add New Rule to create a new blank conditional formatting rule.
Setting conditions
First, select "PC" for "Category" as the first "Setting condition".
Formatting
Next, in formatting, set the taskbar to have a specific color.
Set the application target
Leave the conditional formatting applied to the "whole line".This is to change the color of the bars in Gantt view and the cards in Card view, and the row formatting itself remains the default, so the formatting doesn't change in Grid view.
Note that you typically apply formatting to the entire row of a sheet, or conditional formatting to only certain columns or cells.
Improvements by migrating to smart seats
Real-time processing by the entire team in the cloud
Compared to the case where the Excel sheet of equipment management is stored on the PC of each employee, the members of the team who received the shared sheet of Smartsheet follow their respective authority to check the latest information and check the information. You can update it.
For example, members of business establishments away from the head office can also know the status of equipment at the head office, so if there is excess equipment in stock at the head office, for example, instead of purchasing at their own business office, the equipment at the head office will be transferred. It is also possible to ask for.
Visualization by Gantt view and card view
For example, Gantt View allows you to visually see how long each piece of equipment has been purchased, and what percentage of the order is up to the next replenishment point.
Utilization of file attachment to line
Also, although I did not introduce it here, since files can be attached to the rows of smart sheet sheets, by attaching related files such as warranty cards, specifications, contracts, etc. related to equipment, You can centrally manage information about equipment.
Summary
"Effective utilization of Excel resources by smart sheets", which is considered to be abundant in Japanese organizations, is considered to be very effective as the first step in the digital transformation of organizations.
In this tutorial, we hope that you will understand "Effective utilization of Excel resources by smart sheet" that allows you to efficiently collaborate on the cloud by importing an Excel sheet into a smart sheet and making simple settings. ..
Part XNUMX Utilization of mobile apps (notice)
In the second part, we will explain how to further improve the efficiency of business by utilizing mobile applications and automating with workflow.
Easy warehousing / delivery processing on the spot using the mobile app
The seat of the smart sheet reflects the real-time inventory quantity.You can improve the accuracy of inventory management by preventing omissions and mistakes in paper and Excel ledgers.
Streamline inventory with barcodes and QR codes
Since the mobile app can read the barcode, inventory can be done by reading the barcode and entering the number of stocks.
Since the tabulation work is also performed automatically, the troublesome work after the work is eliminated.
Securely manage history on the smart sheet
Record who bought, used, and replenished equipment, when, and how many.It is also possible to analyze the utilization status of equipment.
If you fall below the order point, you will be notified by email.
If the number of stocks falls below the specified number, an alert email will be sent to the person in charge to prevent out of stock and omission of orders.
Specific realization method (under consideration)
Make a sheet like the one below
If you select a category, a "conditional dropdown" that allows you to select the equipment for that category is desirable, but since it does not currently have that function, create a sheet for each category.
Select the form for each category from the dashboard etc., select the product name, select the processing content, select or enter the number
For example, the processing involves an increase or decrease in the number of new addition registrations, usage number registrations, supplementary registrations, inventory registrations, and the like.
The date and time when the line was created and the creator are automatically recorded.
Aggregate the sheets for each category in a report so that the report's aggregate function calculates the current equipment inventory.