Freeze or Lock Rows and Columns

How Do I Freeze or Lock Rows and Columns in Excel?

You can view two areas of a worksheet and lock rows or columns in one area by freezing or splitting panes. When you freeze panes, you select specific rows or columns that remain visible and immovable when scrolling in the worksheet. For example, you would freeze panes to keep row and column labels visible as you scroll, as shown in the example below where row 1 is frozen.

When you split panes, you create separate worksheet areas that you can scroll within, while rows or columns in the non-scrolled area remain visible.

Steps to Freeze Panes to lock specific rows or columns

On the worksheet, do one of the following:

1. To lock rows, select the row below where you want the split to appear.
2. To lock columns, select the column to the right of where you want the split to appear.
3. To lock both rows and columns, click the cell below and to the right of where you want the split to appear.

On the View tab, in the Window group, click Freeze Panes, and then click the option that you want.

Split panes to lock rows or columns in separate worksheet areas:

1. To split panes, point to the split box at the top of the vertical scroll bar or at the right end of the horizontal scroll bar.
2. When the mouse pointer changes to a split pointer or , drag the split box down or to the left to the position that you want at the centre of the worksheet.
   
3. To remove the split, double-click any part of the split bar that divides the panes.

This would enable you view or compare different parts of worksheet that are far apart in close range. For instance, you can compare data in column B with data in column AZ at the same time.

Access The Ribbon Bar Using Keyboard

Keyboard Access To The Ribbon Bar

To acess the Ribbon Bar using the keyboard, take the following steps:

1. Press ALT
2. The KeyTips are displayed over each feature that is available in the current view.
3. Press the letter that appears in the KeyTip over the feature that you want to use.
4. Depending on which letter you press, additional KeyTips may appear. For example, if the Home tab is active and you press I, the Insert tab is displayed, along with the KeyTips for the groups on that tab.
   
5. Continue pressing letters until you press the letter of the command or control that you want to use. In some cases, you must first press the letter of the group that contains the command.
6. To cancel the action that you are taking and hide the KeyTips, press ALT.

Other Shortcut Keys in Microsoft Excel

This articles describes all the other useful shorcut keys in Microsoft Excel that can enable you perform many functions quickly while working in Microsoft Excel. They keys include the Shift Key combine with the Arrow keys, or combining Alt key with the Enter Key and combining Ctrl, Shift with Enter keys and many other key combinantions.

KEYS	DESCRIPTION
ARROW KEYS	The Arrow keys move one cell up, down, left, or right in a worksheet. CTRL+ARROW keys move to the edge of the current data region in a worksheet. Note that data region is a range of cells that contains data and that is bounded by empty cells or datasheet borders. SHIFT+ARROW key extends the selection of cells by one cell. CTRL+SHIFT+ARROW key extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell. LEFT ARROW or RIGHT ARROW key selects the tab to the left or right when the ribbon is selected. When a submenu is open or selected, these arrow keys switch between the main menu and the submenu. When a ribbon tab is selected, these keys navigate the tab buttons. DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open. When a ribbon tab is selected, these keys navigate up or down the tab group. In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options. DOWN ARROW or ALT+DOWN ARROW opens a selected drop-down list.
BACKSPACE	The Backspace key deletes one character to the left in the Formula Bar. It also clears the content of the active cell. In cell editing mode, it deletes the character to the left of the insertion point.
DELETE	The Delete key removes the cell contents from selected cells without affecting cell formats or comments. In cell editing mode, it deletes the character to the right of the insertion point.
END	The End key moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on. Also selects the last command on the menu when a menu or sub-menu is visible. CTRL+END move to the last cell on a worksheet, in the lowest used row of the rightmost used column. If the cursor is in the formula bar, CTRL+END move the cursor to the end of the text. CTRL+SHIFT+END extend the selection of cells to the last used cell on the worksheet (lower-right corner). If the cursor is in the formula bar, CTRL+SHIFT+END select all text in the formula bar from the cursor position to the end—this does not affect the height of the formula bar.
ENTER	The Enter key completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default). In a data form, it moves to the first field in the next record. Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command. In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button). ALT+ENTER starts a new line in the same cell. CTRL+ENTER fills the selected cell range with the current entry. SHIFT+ENTER completes a cell entry and selects the cell above.
ESC	The ESC key cancels an entry in the cell or Formula Bar. It closes an open menu or sub-menu, dialog box, or message window. It also closes full screen mode when this mode has been applied, and returns to normal screen mode to display the Ribbon and status bar again.
HOME	The Home key moves to the beginning of a row in a worksheet. It also move to the cell in the upper-left corner of the window when SCROLL LOCK is turned on. Selects the first command on the menu when a menu or submenu is visible. CTRL+HOME move to the beginning of a worksheet. CTRL+SHIFT+HOME extend the selection of cells to the beginning of the worksheet.
PAGE DOWN	The Page Down key move one screen down in a worksheet. ALT+PAGE DOWN move one screen to the right in a worksheet. CTRL+PAGE DOWN move to the next sheet in a workbook. CTRL+SHIFT+PAGE DOWN select the current and next sheet in a workbook.
PAGE UP	The Page Up key move one screen up in a worksheet. ALT+PAGE UP move one screen to the left in a worksheet. CTRL+PAGE UP move to the previous sheet in a workbook. CTRL+SHIFT+PAGE UP select the current and previous sheet in a workbook.
SPACEBAR	In a dialog box, the Spacebar key performs the action for the selected button, or selects or clears a check box. CTRL+SPACEBAR select an entire column in a worksheet. SHIFT+SPACEBAR select an entire row in a worksheet. CTRL+SHIFT+SPACEBAR select the entire worksheet. Important. a. If the worksheet contains data, CTRL+SHIFT+SPACEBAR select the current region. Pressing CTRL+SHIFT+SPACEBAR a second time select the current region and its summary rows. Pressing CTRL+SHIFT+SPACEBAR a third time select the entire worksheet. b. When an object is selected, CTRL+SHIFT+SPACEBAR select all objects on a worksheet. ALT+SPACEBAR display the Control menu for the Microsoft Office Excel window.
TAB	The Tab key moves one cell to the right in a worksheet. Moves between unlocked cells in a protected worksheet. It also moves to the next option or option group in a dialog box. SHIFT+TAB move to the previous cell in a worksheet or the previous option in a dialog box. CTRL+TAB switche to the next tab in dialog box. CTRL+SHIFT+TAB switche to the previous tab in a dialog box.

 

F1 to F12 Function Keys: Microsoft Excel

What Do The F1 to F12 Function Keys Do in Microsoft Excel?

The following article explains all the function of the Function Key in Microsoft Excel, starting from F1 to F12 keys when used alone and when used in combination with the CTRL key, SHIFT Key and the ALT Key. You can always visit this blog for other function you may require and if the action you perform often does not have a function key for it, you can record a macro to perform it. Later on on this blog, I will post how to record a macro for such activity.

KEY	DESCRIPTION
F1	F1 Key displays the Microsoft Office Excel Help task pane. CTRL+F1 displays or hides the ribbon. ALT+F1 creates a chart of the data in the current range. ALT+SHIFT+F1 inserts a new worksheet.
F2	F2 Key edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off. SHIFT+F2 adds or edits a cell comment. CTRL+F2 displays the Print Preview window.
F3	F3 Key displays the Paste Name dialog box. SHIFT+F3 displays the Insert Function dialog box.
F4	F4 Key repeats the last command or action, if possible. CTRL+F4 closes the selected workbook window.
F5	F5 Key displays the Go To dialog box. CTRL+F5 restores the window size of the selected workbook window.
F6	F6 Key switches between the worksheet, ribbon, task pane, and Zoom controls. In a worksheet that has been split (View menu, Manage This Window, Freeze Panes, Split Window command), F6 includes the split panes when switching between panes and the ribbon area. SHIFT+F6 switches between the worksheet, Zoom controls, task pane, and ribbon. CTRL+F6 switches to the next workbook window when more than one workbook window is open.
F7	F7 Key displays the Spelling dialog box to check spelling in the active worksheet or selected range. CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ENTER, or ESC to cancel.
F8	F8 Key turns extend mode on or off. In extend mode, Extended Selection appears in the status line, and the arrow keys extend the selection. SHIFT+F8 enables you to add a nonadjacent cell or range to a selection of cells by using the arrow keys. CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized. ALT+F8 displays the Macro dialog box to create, run, edit, or delete a macro.
F9	F9 Key calculates all worksheets in all open workbooks. SHIFT+F9 calculates the active worksheet. CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. CTRL+F9 minimizes a workbook window to an icon.
F10	F10 Key turns key tips on or off. SHIFT+F10 displays the shortcut menu for a selected item. ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message. CTRL+F10 maximizes or restores the selected workbook window.
F11	F11 Key creates a chart of the data in the current range. SHIFT+F11 inserts a new worksheet. ALT+F11 opens the Microsoft Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
F12	F12 Key displays the Save As dialog box. Likewise in all Microsoft Office applications.

 

Microsoft Excel CTRL-SHIFT Combination Shortcut Keys

Microsoft Excel CTRL-SHIFT Combination Shortcut Keys:
Shortcut Keys to Work Smarter in Excel

The following table contains both CTRL and SHIFT combination shortcut keys along with descriptions of their functionality which one can use in Microsoft Excel to work more proficiently. Mastering these key will enable Excel user to skilfully enhance the speed and accuracy or work. The CTRL and SHIFT key can combine any other key on the keyboard, both the alphabet and number keys to perform special functions embedded in them. Constant usage and practice will enhance expertise in the use of these shortcut keys.

CTRL and SHIFT Combination Shortcut Keys

KEY	DESCRIPTION
CTRL+SHIFT+(	CTRL+SHIFT+( unhide any hidden rows within the selection.
CTRL+SHIFT+)	CTRL+SHIFT+) unhide any hidden columns within the selection.
CTRL+SHIFT+&	CTRL+SHIFT+& apply the outline border to the selected cells.
CTRL+SHIFT+_	CTRL+SHIFT+ remove the outline border from the selected cells.
CTRL+SHIFT+~	CTRL+SHIFT+~ apply the General number format.
CTRL+SHIFT+$	CTRL+SHIFT+$ apply the Currency format with two decimal places (negative numbers in parentheses).
CTRL+SHIFT+%	CTRL+SHIFT+% apply the Percentage format with no decimal places.
CTRL+SHIFT+^	CTRL+SHIFT+^ apply the Exponential number format with two decimal places.
CTRL+SHIFT+#	CTRL+SHIFT+# apply the Date format with the day, month, and year.
CTRL+SHIFT+@	CTRL+SHIFT+@ apply the Time format with the hour and minute, and AM or PM.
CTRL+SHIFT+!	CTRL+SHIFT+! apply the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
CTRL+SHIFT+*	CTRL+SHIFT+* select the current region around the active cell (the data area enclosed by blank rows and blank columns). In a PivotTable, it selects the entire PivotTable report.
CTRL+SHIFT+:	CTRL+SHIFT+: enter the current time.
CTRL+SHIFT+"	CTRL+SHIFT+" copy the value from the cell above the active cell into the cell or the Formula Bar.
CTRL+Plus (+)	CTRL+Plus (+) display the Insert dialog box to insert blank cells.
CTRL+Minus (-)	CTRL+Minus (-) display the Delete dialog box to delete the selected cells.
CTRL+;	CTRL+; enter the current date.
CTRL+`	CTRL+` alternate between displaying cell values and displaying formulas in the worksheet.
CTRL+'	CTRL+' copy a formula from the cell above the active cell into the cell or the Formula Bar.
CTRL+1	CTRL+1 display the Format Cells dialog box.
CTRL+2	CTRL+2 apply or removes bold formatting.
CTRL+3	CTRL+3 apply or removes italic formatting.
CTRL+4	CTRL+4 apply or remove underlining.
CTRL+5	CTRL+5 apply or remove strikethrough.
CTRL+6	CTRL+6 alternate between hiding objects, displaying objects, and displaying placeholders for objects.

CTRL+8	CTRL+8 display or hides the outline symbols.
CTRL+9	CTRL+9 hide the selected rows.
CTRL+0	CTRL+0 hide the selected columns.
CTRL+A	CTRL+A select the entire worksheet. If the worksheet contains data, CTRL+A select the current region. Pressing CTRL+A the second time select the current region and its summary rows. Pressing CTRL+A the third time select the entire worksheet. When the insertion point is to the right of a function name in a formula, display the Function Arguments dialog box. CTRL+SHIFT+A insert the argument names and parentheses when the insertion point is to the right of a function name in a formula.
CTRL+B	CTRL+B applie or remove bold formatting.
CTRL+C	CTRL+C Copy the selected cells. CTRL+C followed by another CTRL+C display the Clipboard.
CTRL+D	Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+F	CTRL+F displays the Find and Replace dialog box, with the Find tab selected. SHIFT+F5 also display this tab, while SHIFT+F4 repeat the last Find action. CTRL+SHIFT+F open the Format Cells dialog box with the Font tab selected.
CTRL+G	CTRL+G display the Go To dialog box. F5 also displays the same Go To dialog box.
CTRL+H	CTRL+H display the Find and Replace dialog box, with the Replace tab selected.
CTRL+I	CTRL+I apply or remove italic formatting.
CTRL+K	CTRL+K display the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.
CTRL+N	CTRL+N Create a new, blank workbook.
CTRL+O	CTRL+O display the Open dialog box to open or find a file. CTRL+SHIFT+O select all cells that contain comments.
CTRL+P	CTRL+P displays the Print dialog box. CTRL+SHIFT+P open the Format Cells dialog box with the Font tab selected.
CTRL+R	CTRL+R use the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
CTRL+S	CTRL+S save the active file with its current file name, location, and file format.
CTRL+T	CTRL+T displays the Create Table dialog box.
CTRL+U	CTRL+U apply or remove underlining. CTRL+SHIFT+U switch between expanding and collapsing of the formula bar.
CTRL+V	CTRL+V Insert the contents of the Clipboard at the insertion point and replace any selection. Available only after you have cut or copied an object, text or cell contents.
CTRL+W	CTRL+W Close the selected workbook window.
CTRL+X	CTRL+X Cut the selected cells.
CTRL+Y	CTRL+Y repeat the last command or action, if possible.
CTRL+Z	CTRL+Z use the Undo command to reverse the last command or to delete the last entry that you typed. CTRL+SHIFT+Z use the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.

How Do I Save Workbook in PDF?

Saving Workbook in PDF (Portable Document Format)

PDF is a fixed-layout electronic file format that preserves document formatting and enables file sharing. The PDF ensures that when the file is viewed online or printed, it retains exactly the format that you intended, and that data in the file cannot easily be changed. The PDF is also useful for documents that will be reproduced by using commercial printing methods.

To view a PDF file, you must have a Adobe Acrobat Reader or other PDF reader installed on your computer. When a workbook is saved as PDF, you cannot use your MS Excel that created it to make changes directly to the file. You must make changes to the original workbook in the MS Office release program in which you created it and save the file as PDF again. You can save as a PDF from a Microsoft Office Excel 2007 only after you install an add-in which you will get from this website: microsoft.com and follow the instructions on that page to istall the add-in. However, the download is available to only customers running genuine Microsoft Office software.

Now you can take the following steps to save a workbook in PDF:

METHOD 1:

1. Open the Workbook you want to convert to PDF, click the Microsoft Office button and click Print.
2. From the Printer Name box select the PDF printer from the list of available printers and click Ok.
3. The PDF window appears. Enter a name and location for the PDF file. You can also set other PDF options at this time.
4. Click Create PDF.

METHOD 2:

1. Click the Microsoft Office Button or Click File on the Menu Bar, point to the arrow next to Save As or Click Save As, and then click PDF.
2. In the File Name list, type or select a name for the workbook.
3. In the Save as type list, click PDF.
4. If you want to open the file immediately after saving it, select the Open file after publishing check box. This check box is available only if you have a PDF reader installed on your computer.
5. Next to Optimize for, do one of the following, depending on whether file size or print quality is more important to you:
1. If the workbook requires high print quality, click Standard (publishing online and printing).
2. If the print quality is less important than file size, click Minimum size (publishing online).
6. Click Publish.

 

METHOD 3:

1. Click File on the menu bar, click Save As.
2. In the File Name list, type or select a name for the workbook.
3. In the Save as type list, click PDF.
4. If you want to open the file immediately after saving it, select the Open file after publishing check box. This check box is available only if you have a PDF reader installed on your computer.
5. Click Save.

How Do I Work in Microsoft Excel?

Working in Microsoft Excel

To be able to work effectively if MS Excel, one need to be able to navigate the worksheet. This can be done by using the arrow keys, the scroll bars, or the mouse to move between cells and to move quickly to different areas of the worksheet. In Microsoft Office Excel 2007, you can take advantage of increased scroll speeds, easy scrolling to the end of ranges, and tooltips that let you know where you are in the worksheet. There are different ways to scroll through a worksheet. You can use the arrow keys, the scroll bars, or the mouse to move between cells and to move quickly to different areas of the worksheet. You can also use the mouse to scroll in dialog boxes that have drop-down lists with scroll bars.

To scroll	Do this
To the start and end of ranges	Press CTRL+ARROW key to scroll to the start and end of each range in a column or row before stopping at the end of the worksheet. To scroll to the start and end of each range while at the same time, selecting the ranges before stopping at the end of the worksheet, press CTRL+SHIFT+ARROW key.
One row up or down	Press SCROLL LOCK on the keyboard, and then use the UP ARROW key or DOWN ARROW key to scroll one row up or down.
One column left or right	Press SCROLL LOCK, and then use the LEFT ARROW key or RIGHT ARROW key to scroll one column left or right.
One window up or down	Press PAGE UP or PAGE DOWN.
One window left or right	Press SCROLL LOCK, and then hold down CTRL while you press the LEFT ARROW or RIGHT ARROW key.
A large distance	Press SCROLL LOCK, and then simultaneously hold down CTRL and an arrow key to quickly move through large areas of your worksheet.