• Inserting drop-down - Data Validation
• Frequently used Excel formulas
• Freeze/unfreeze rows/colums
• Count unique texts in a range with COUNTIF
• How to use Pivot table for consistency check
• EXCEL Tutorials and useful links

NOT MOVED TO ODOO, DOES NOT SEEM VERY HELPFUL (AND COULD NOT EVEN FIGURE OUT HOW THIS WORKS). FOR THIS PURPOSE, ONE CAN USE PROTECTION, SEE ARTICLE BELOW.

NO CONTENT TO TRANSFER

VIDEO clip on to be inserted

TRANSFERRED TO ODOO IN UPDATED FORM. IF YOU WISH TO MAKE CHANGES, PLEASE CONTACT LAURA OR SHINOH. 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.

NO CONTENT TO TRANSFER

NOT TRANSFERRED AS NOT CLEAR IF THIS IS HELPFUL - IS THIS ONLY ABOUT UNPROTECTING THE SHEET?

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.

MOVED TO ODOO. IF YOU WISH TO MAKE CHANGES, CONTACT LAURA OR SHINOH. 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.

THIS AND THE TWO BELOW HAVE NO CONTENT TO TRANSFER.

THE BELOW ARTICLE HAS BEEN MOVED TO ODOO. IF YOU WISH TO MAKE CHANGES, PLEASE CONTACT SHINOH OR LAURA.

How to save CSV as Excel

MOVED TO ODOO UNDER 'USEFUL RESOURCES FOR 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

MOVED TO ODOO. IF YOU WISH TO MAKE CHANGES, PLEASE CONTACT LAURA OR SHINOH.

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”.