Microsoft Excel: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(11 intermediate revisions by the same user not shown)
Line 7: Line 7:


=Subjects=
=Subjects=
 
* [[General Functions in Excel]]
* [[Time and Date Functions in Excel]]
* [[Time and Date Functions in Excel]]
* [[Conditional Sum in Excel]]
* [[Conditional Sum in Excel]]
Line 14: Line 14:
* [[Excel Get a Column Value Corresponding to A Value Match in Another Column|Get a Column Value Corresponding to a Value Match in Another Column]]
* [[Excel Get a Column Value Corresponding to A Value Match in Another Column|Get a Column Value Corresponding to a Value Match in Another Column]]
* [[Python Support in Excel]]
* [[Python Support in Excel]]
* [[Last Non-Empty Cell in a Column in Excel]]
* [[nth Root of a Number in Excel]]
* [[Stock Data Support in Excel]]


=Operations=
=Operations=
==Freeze Rows==
==Freeze Rows==
'''To freeze the first two top rows''': select the '''entire''' row '''under''' the rows you want to freeze. If you want two freeze the first top rows, select the third row by clicking on the row index, this will select the entire row. Then click "Freeze Panes".


Select a cell below the rows and to the right of the columns you want to freeze. Then go to View → Freeze Panes → Freeze Panes.
Select a cell below the rows and to the right of the columns you want to freeze. Then go to View → Freeze Panes → Freeze Panes.
==Link to a Specific Tab and Cell in Another Excel Document==
Ctrl -K → Web Page or File → Navigate to the Excel file and use its name in the Address field → Append #'Tab name even if includes spaces'!Cell.
Example:
<font size=-2>
Investments.xlsx#'FENY (M)'!A1
</font>
==Edit the Same Cell in Multiple Worksheets==
Select the first worksheet you want to edit.
Hold down the Shift key as you click on the additional worksheets you want to edit (alternatively you can [[#How_to_Select_All_Worksheets|select all worksheets]] and just de-select those you don't want to edit). Excel will add the word "Group" to the title bar to indicate you have a group of worksheets.
Make the change in the first worksheet. The change will propagate to all selected worksheets.
<font color=darkkhaki>Does not seem to work with cells that contain hyperlinks.</font>
==How to Select All Worksheets==
Control + Click on one worksheet tab → Menu → Select All Sheets
To deselect one of all: Command + click the worksheet tab.

Latest revision as of 21:45, 10 March 2024

Internal

Overview

Subjects

Operations

Freeze Rows

To freeze the first two top rows: select the entire row under the rows you want to freeze. If you want two freeze the first top rows, select the third row by clicking on the row index, this will select the entire row. Then click "Freeze Panes".

Select a cell below the rows and to the right of the columns you want to freeze. Then go to View → Freeze Panes → Freeze Panes.

Link to a Specific Tab and Cell in Another Excel Document

Ctrl -K → Web Page or File → Navigate to the Excel file and use its name in the Address field → Append #'Tab name even if includes spaces'!Cell.

Example:

Investments.xlsx#'FENY (M)'!A1

Edit the Same Cell in Multiple Worksheets

Select the first worksheet you want to edit.

Hold down the Shift key as you click on the additional worksheets you want to edit (alternatively you can select all worksheets and just de-select those you don't want to edit). Excel will add the word "Group" to the title bar to indicate you have a group of worksheets.

Make the change in the first worksheet. The change will propagate to all selected worksheets.

Does not seem to work with cells that contain hyperlinks.

How to Select All Worksheets

Control + Click on one worksheet tab → Menu → Select All Sheets

To deselect one of all: Command + click the worksheet tab.