Go to cApStAn HOME
Go To General Reference Home

EXCEL QUICK REFERENCE

Stop rows being inserted or deleted in Excel

Freezing/Unfreezing Panes

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

VIDEO clip to be inserted

Data > Group/Ungroup

VIDEO clip on to be inserted

Locking cells: Home > Font

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

How to remove the password encryption

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)

Data > Data Validation

Combine data from 2 cells (e.g. text)

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.

Input message

Error alert in Data validation

Conditional formatting

How to convert CSV

How to save CSV as Excel

Pivot Tables

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

How to use Pivot table for consistency check - by Tanya

PART 1 PART 1

PART 2 PART 2

PART 3 PART 3

Excel files used in the video clips:

Adaptations

Attribute

Identical segments

The PowerPoint used in the video clips

Extra Quotation Marks

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