Archive List for July 2017

Calculating date, time/comparison, addition and subtraction

Basic Course

Addition and subtraction of dates, times, minutes Here we will explain about the addition/subtraction of ‘dates’ and ‘times’, something we often use in Excel.   How to calculate the date) Addition・Subtraction The list below shows members’ information for a video rental shop. The expiry date for membership is 1 year, and 2 weeks prior to that・・・

Create, select from, and deactivate drop-down lists

Basic Course

How to create a drop-down list If we realize that we are using the same words repeatedly in a table, it might be more efficient to use a drop-down list. Here, we will explain how to use the ‘Data Validation’ to create drop-down lists. What is a drop-down list? A drop-down list allows us to select words/values・・・

Set a password/Book・File, Worksheet, Cell range

Basic Course

Set a password to a book/file For Excel books/files that are private and want to prevent other people’s access, we can set a password to the book to protect them.   How to set it up) Protect Excel book with a password We can set a password during the ‘Save’ or ‘Save As’ process. (1) Open ‘Save’・・・

INDIRECT/Refer to value as text string and extract to another cell

Basic Course

How to use INDIRECT The INDIRECT function displays the selected cell address as a text string. =INDIRECT(ref_text,[a1]) [ref_text] = Select cell address to reference. [a1] = Not obligatory. Identify the reference format to use function for [ref_text]. 「TRUE, or nothing written」・・・Reference format in A1. 「FALSE」・・・Reference format R1C1. Definition of function A function to display the selected cell・・・

Format cell/text color based on drop-down lists and values

Basic Course

How to change cell color for every Saturday・Sunday・holiday When managing Excel data, one important aspect other than formulas will be ‘cell color and text color‘. It makes the data more easier to look at. Let us look at ‘conditional formatting’ that changes the color of cells and texts based on specified conditions.   Example of use・・・

Shortcuts/Commands to make repeated tasks efficient

Basic Course

What is a shortcut? A shortcut is an operation that makes a move with many steps much more efficient, most of the times with a press on the keyboard or a click on the mouse. The combination of keys that create a shortcut are called ‘Shortcut keys’. How to use the shortcut key) In Windows・・・

Add, subtract, multiply, divide, and round (up and down)

Basic Course

How to add・subtract・multiply・divide, and calculate division remainder Being recognized as a ‘spreadsheet program’, Excel is strong in various types of calculations. Here we will explain about the basics of Excel, the ‘four arithmetic operations‘. Example of use 1) Four arithmetic operations Below is a sales report of a Bakery shop. We will calculate the total sold amount・・・

Relative, absolute, mixed reference/$ fix, collective set up

Basic Course

Relative cell reference What is a relative cell reference? When a formula is copied to another cell, the relative cell reference (a form of reference) automatically changes the originally identified cell in the formula according to how much it moved from the original spot to the new (copied) spot. Now we will look at examples・・・

Make, save and use TSV and CSV files

Basic Course

What does CSV stand for? Sometimes the word ‘CSV’ might come up when discussing about Excel data. ‘CSV’ is an abbreviation for Comma Separated Value and are values separated by commas. In other words, it is a file created with each chunk of information separated with ‘, (commas)’. This CSV file serves as an intermediary・・・

To the top