# Archive List for July 2017

## Calculating date, time/comparison, addition and subtraction

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

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

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

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

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

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・・・

## Check cell information/links, data type, color, row and column

Find where the link in a cell comes from In Excel data, there are times when we might pull out data from a different Excel file (link). If that is only for only 1 or 2 chunks of data that might not be a problem, but if there are many links, then we might question・・・

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

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

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

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・・・