//**[[:start|Go to cApStAn HOME]]**// \\
//**[[generalreference:start#general_reference|Go To General Reference Home]]**//


===== EXCEL QUICK REFERENCE =====

  * [[+tab|:generalreference:excel_datavalid|Inserting drop-down - Data Validation]] 
  * [[+tab|:generalreference:excel_formulas|Frequently used Excel formulas]] 
  * [[+tab|:generalreference:excel_freeze|Freeze/unfreeze rows/colums]] 
  * [[+tab|:generalreference:excel_countif|Count unique texts in a range with COUNTIF]] 
  * [[+tab|:generalreference:excel_pivotconcistency|How to use Pivot table for consistency check]]
  * [[+tab|:generalreference:excel_links|EXCEL Tutorials and useful links]]

===== Stop rows being inserted or deleted in Excel =====
<color #ed1c24>**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.**</color>

{{:generalreference:excel:stop_rows_being_inserted_or_deleted_in_excel.png|}}

{{:generalreference:excel:dialogue.png|}}
----


==== Data > Group/Ungroup ====
<color #ed1c24>**NO CONTENT TO TRANSFER**</color>

//VIDEO clip on to be inserted//

----

==== Locking cells: Home > Font ====
**<color #ed1c24>TRANSFERRED TO ODOO IN UPDATED FORM. IF YOU WISH TO MAKE CHANGES, PLEASE CONTACT LAURA OR SHINOH.</color>**
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.

----

==== Protect sheet: Review> Protect sheet ====
<color #ed1c24>**NO CONTENT TO TRANSFE**R</color>
----

==== How to remove the password encryption ====
**<color #ed1c24>NOT TRANSFERRED AS NOT CLEAR IF THIS IS HELPFUL - IS THIS ONLY ABOUT UNPROTECTING THE SHEET?</color>**

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.

----

==== =IF(condition;result if condition is met; result if condition is not met) ====

----


==== Combine data from 2 cells (e.g. text) =====
<color #ed1c24>**MOVED TO ODOO. IF YOU WISH TO MAKE CHANGES, CONTACT LAURA OR SHINOH.**</color>
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.
{{ :generalreference:combine_formula.jpg?nolink&900 |}}

----
===== Input message =====
<color #ed1c24>**THIS AND THE TWO BELOW HAVE NO CONTENT TO TRANSFER.**</color>
----

==== Error alert in Data validation ====

----


==== Conditional formatting ====

----

==== How to convert CSV ====
**<color #ed1c24>THE BELOW ARTICLE HAS BEEN MOVED TO ODOO. IF YOU WISH TO MAKE CHANGES, PLEASE CONTACT SHINOH OR LAURA</color>.**

[[tecdoc:csv2xls|How to save CSV as Excel]]
----

===== Pivot Tables =====
<color #ed1c24>**MOVED TO ODOO UNDER 'USEFUL RESOURCES FOR EXCEL'**.</color>

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:

[[http://www.excelfunctions.net/Excel-Pivot-Table-Tutorial.html|External Link]]


==== Extra Quotation Marks ====
<color #ed1c24>**MOVED TO ODOO. IF YOU WISH TO MAKE CHANGES, PLEASE CONTACT LAURA OR SHINOH.**</color>

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: {{ :generalreference:excel: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”.\\