Good tutorial sites:

http://www.excel-easy.com

https://exceljet.net/

VBA (Visual Basic Application): https://www.youtube.com/watch?v=ABXPb0qnKUY

Formula: SUMPRODUCT(1/COUNTIF(data range,data range))

On the View tab, in the Window group, click the arrow below Freeze Panes.

VIDEO clip to be inserted

Data > Data Validation

VIDEO clip on to be inserted

Step 1

In the beginning the idea is not to ‘lock’ cells but first to ‘unlock’ ALL cells (cells are ‘locked’ by default).

Select all, right-click the selection, select ‘Format Cells, then the ‘Protection’ tab, un-tick the ‘Locked’ box, click OK.

Step 2

The next stage is to lock columns A, B, C (for example) only.

Select columns A, B, C, right-click the selection, select ‘Format Cells’, then the ‘Protection’ tab

Tick the ‘Locked’ box and then click OK

Step 3

Then, in the ‘Review’ menu tab, select ‘Protect Sheet’, un-tick ‘Select locked cells’

Tick “Format cells” and “Insert rows”.

Then enter a password and re-enter the password.

In the workbook you want to remove the password encryption, click File > Info, click the Protect Workbook button, and select Encrypt with Password in the drop-down menu.

The Encrypt Document window will appear, you clear the password in the Password box, and click OK.

Save the document.

You can combine data from multiple cells into a single cell using the Ampersand symbol (&).

Combine data with the Ampersand symbol (&)

• Select the cell where you want to put the combined data.
• Type = and select the first cell you want to combine.
• Type & and use quotation marks with a space enclosed.
• Select the next cell you want to combine and press enter. An example formula might be =A2&“ ”&B2.

How to save CSV as Excel

There are many tutorials on the Web explaining what a pivot table is and how to create one.

Here is a link to a good one:

External Link

PART 1

PART 2

PART 3

Excel files used in the video clips:

• Adaptations
• Attribute
• Identical segments

The PowerPoint used in the video clips

When copying cells that contain multiple lines separated by one or several line breaks, the pasted text will have extra quotation marks.

If the cells are copied into MS Word, there are several ways to remove these unwanted quotation marks only, without affecting other quotation marks that need to be in the original text.

One method is the following:

1) Paste the content copied from Excel into MS Word with ctrl-V. It will be pasted as a table (keeping the table structure as in Excel).
2) Select the entire table by clicking the “+” sign on the upper-left corner.
3) Go to the menu Table Tools > Layout and click on “Convert to Text”, Check “Separate text with Paragraph marks”.
4) Select the converted text entirely once again and go to “Find and Replace”.
5) Click “More”.
6) In “Find what:”, type ^l (it is the “Manual line break” from the “Special” menu).
7) In “Replace with”, type ^p (it is the “Paragraph Mark” from the “Special” menu).

Another way to remove the unwanted extra quotations marks is to use the following Excel macro: quote_removal.xlsm

Instructions:

1) Open the excel file “quote_removal.xlsm”. Leave it open in background.
2) Select the cells that you need to copy from your excel file.
3) Instead of using “ctrl-C” to copy, use “ctrl-shift-T”.
4) Paste your cells into whatever you need to paste the text onto (Word/Excel/Notepad/Outlook/etc) as usual with “ctrl-V”.