Free PC Repair and Tools
ZettaLogix.com
Excel Tips and Tricks
Excel Tips and Tricks Of The Month
Ever need to add a degrees symbol to your worksheet?
It is not part of the standard cell format choices (e.g. 76°).
Ever need to add a degrees symbol to your worksheet?
It is not part of the standard cell format choices (e.g. 76°).
Solution
Press Alt+248 – Make sure your Number Lock is enabled.
Thanks Ramesh S. (India)
Press Alt+248 – Make sure your Number Lock is enabled.
Thanks Ramesh S. (India)
Have a tip to share? Need help identifying a solution? Send us an e-mail... ____________ Other Free Excel Tools ____________
E-Mail ZettaLogix Now
OTHER TIPS
________________
______________
.gif)
_____________
____________
| Task | Excel Tips and Tricks Solution |
| Ever need to add a line-break within a cell (add second/third line in the same cell)? | When using excel, you can insert a line-break by pressing ALT +Enter. This will create a second row in the same cell. Add as many as needed.... |
| Want to import data or a table from a web page into an Excel spreadsheet? | Need to include data from a web page in a spreadsheet? Select the Data menu - then, Import External Data - then, New Web Query. Select the table or data from the web page you want to import by choosing the yellow arrow button next to the table. Press the Import button on the New Web Query dialog box. Press the OK button on the Import Data dialog box. Save the Excel file and use the Refresh button in the External Data Tool-bar to get updated data at another time from the same Web Page. To display the External Data Tool-bar: Choose View => Tool-bars => External Data. Hit the Refresh button near middle of bar. |
| Need a quick way to add formulas to several cells down a row (or columns) at the same time? | Select en empty cell that you would like to add a formula. After you've created the formula, hit "Enter". Next, highlight the cell that you created your formula in, and look for a small black box at the bottom right corner of the cell. With your mouse, you can click on the small box (your mouse cursor will turn into a "+" sign), and drag down the page. This will add the same formula to as many cells as you choose. Remember to freeze the cells you don't want to shift. |
| Adding a calculator icon to your toolbar. | Right click in any of the empty space in the tool-bar at the top of Excel. This will bring up the Tool-bar menu. Choose Customize from the bottom of the menu. This will bring up the Customize window. Select the Commands tab in the Customize window. Choose Tools from the left side of the window. This will bring up a list of commands on the right side of the window. Scroll down until you see a calculator icon next to the word Custom. This should be just below a camera icon, and just above the Solitaire icon (cleverly labeled Custom as well). Click on the Custom calculator and drag it onto your tool-bar. When you click on it, a calculator will pop up. To get the calculator off your tool-bar, open the customize window just as you did to get the calculator, and click and drag it back to the Tools section. |
| Want to quickly spell-check a section of your excel file? | Highlight the area that you would like spell check, and hit the "F7" key. |
| Need a quick way to select a row or a column in Excel? | Selecting all the cells in a column or a row can be difficult using a mouse. An easier way is to use your keyboard to select cells. Ctrl + Space-bar selects a column. Shift + Space-bar selects a row. |
| Need to add a time stamp to a cell in an Excel spreadsheet? | You can add a time-stamp in a specific cell quite easily. From the cell, press Ctrl + Shift + ; |
| Ever have trouble reading the icons on your Excel toolbar? Increase the size of your icons. | You can enlarge the buttons on your toolbar so they are easier to read. From within Excel, go to Tools => Customize. Select the Options tab. In the Other section, check the Large icons box. Select Close. Note: This tip also works for other Office Applications. |
| Ever need to enter the same text in multiple cells using Excel? | Instead of trying to enter the text into individual cells, you can have Excel do it for you. From within Excel, enter the value into the first cell. Select that cell, along with as many other cells in the column where you want to fill down the text. Press Ctrl + D. |
| Need to open a new workbook in Excel? | Often, when you're working in Excel, you might need to open a new workbook to paste content from your current worksheet. You can easily open a new workbook using your keyboard. From within the Excel workbook you currently have open, press Ctrl + N. |
| Need to remove hyperlinks from your Excel worksheet? | When working in Excel, you may need to delete hyperlinks that have been added to a worksheet. To remove a hyperlink from a single URL in a worksheet: Point your mouse to the cell that contains the hyperlink. Right click, and choose Hyperlink => Remove Hyperlink from the pop-up menu. |
| Ever need to tab backwards in Excel? | When you're working on a spreadsheet, and you need to go back to a previous cell to make changes, the following shortcut acts like a backwards tab to your previous cell: Shift + Tab |
| Ever need to change a column to a row in Excel? | When you're working in Excel, you may find you need a new view that incorporates the current columns you have, and breaks those down in rows. You don't have to re-type in each of the cells. Excel has a feature that allows you to change columns to rows or rows to columns. Here's how: Select the cells you want to switch. Press Ctrl + C to copy them to your clipboard. Place your cursor in the cell where you want the new row or column to start. Select OK. Select the Transpose check box. Go to Edit => Paste Special. |
| When you have long Excel spreadsheets, navigating can sometimes be a challenge. | Here are a few shortcuts for finding what you need in those long spreadsheets: Last occupied cell in a column: Ctrl + (down arrow). First occupied cell in a column: Ctrl + (up arrow). Last occupied cell in a row: Ctrl + (right arrow). First occupied cell in a row: Ctrl + (left arrow). |
| Ever need to capture part of an Excel spreadsheet as a graphic to use in another document? | Converting Excel spreadsheets to another file type or presentation graphic, and capture as a picture file. Highlight the area to be included in the graphic. Hold the Shift key down and go Edit - Copy Picture. Select the Picture format for an RTF file, or BMP format for a Bitmap file. Click OK to copy the graphic to your clipboard. Simply past (CTRL V) into other document of web site. |
| Have you ever lost hours of work when Excel crashes? | To configure the AutoSave feature: Select Tools - AutoSave… Configure the available options such as: AutoSave frequency in minutes. Prompt before saving (recommended as this feature saves directly over the original file) Active or All workbooks. Click OK. Excel will now automatically save your work based on the parameters you set. No more lost data. |
| Ever Need to reference two Excel workbooks at the same time? | Are you tired of switching between workbooks? Excel has a feature that allows you to view at least two workbooks or worksheets at the same time. Open the workbook you want to view. Go to Windows => New Window. Excel makes a copy of the current workbook where you can choose worksheets to view. Additionally, you can open another work book from another location. Go to Windows => Arrange... The Arrange function allows you to choose how you want to view the multiple files (tiled, horizontal, vertical, or cascade) |
| Need to add a line break within an Excel cell? | When using Excel, you can insert a line break within a cell. Press Alt + Enter. |
| When you're working in Excel, you can make your work easier to read by applying shading to alternate rows. | To do this: Select the rows to which you want to apply the shading. Go to Format => Conditional Formatting. Change the Condition 1 field to Formula is. In the formula field enter: =MOD(ROW(),2)=0 Click on the Format button and select the Patterns tab. Choose the color you want to use and then click OK. |
| Need a simple way to add drop-down menus to your Excel Spreadsheet columns: | Open your Excel spreadsheet. From the Data menu, select Filter=>AutoFilter. Small drop-down arrows are added at the top of each column in your spreadsheet. The filter sorts the data into drop-down lists. Click the arrows to select the data you want to see. You can also make the entire list reappear by selecting the drop-down arrow again, scrolling to the top, and selecting (All). |
| Want to provide cell comments within an Excel spreadsheet? | When sending an Excel spreadsheet to someone, it's helpful to put a comment next to certain cells. Highlight a cell where you want a comment. Right mouse click and select Insert Comment from the menu. A comment box appears where you can type your comments for the cell. Click anywhere on the spreadsheet to exit the comment box. Keyboard shortcut: Highlight a cell and press SHIFT + F2 on the keyboard. |
| Need to find which formulas in a spreadsheet are linked to another workbook? | Sometimes you might need to find which cells an Excel workbook are linked to another workbook. Here's how: On the Edit menu, click Find. In the Find what box, enter [ (open square bracket). Click Options. In the Within box, click Workbook. In the Look in box, click Formulas. Click Find All. A list of formulas that contain [ (i.e.., that are linked to another workbook) appears in the box at the bottom. Click on any of these to go to cell where the formula appears. |
| Want to add a header or footer to all the worksheets in your Excel document? | When working in Excel, you may wish to customize all the worksheets in your document to keep a consistent look. Highlight the first worksheet. While holding down the control key, click on the tabs at the bottom of other worksheets to highlight them. Select View => Header and Footer. Choose a header and footer from the appropriate pull down menu, or create your own custom header or footer. Using the same technique, you can also control other elements within the selected worksheets, including page orientation, margins, and more. |
| Need to select non-contiguous cells or ranges in a spreadsheet? | Sometimes when you're working in a spreadsheet, you might need to select non-contiguous cells or ranges at the same time (for example, the ranges B2:C10 and E5:G15). Fortunately, there's an easy way in Excel: Hold down the Ctrl key while selecting your ranges. Now both ranges are selected. |
| Need to quickly filter information in a spreadsheet? | Sometimes you may need to quickly sort through large amounts of data (customer information, part numbers, etc.) Highlight the column you wish to filter. Go to Data => Filter => Auto filter. This creates a dropdown in your header row. Click on the dropdown and scroll through the list until you find what you are searching for. |
| Need to enter consecutive numbers in Excel? | Here's an easy way to have Excel automatically enter consecutive numbers in your spreadsheet: Enter the first number of your sequence in a cell. Press and hold the Ctrl key. While the Ctrl key is pressed, click the cells fill handle (small black square in the lower right corner) and drag it over the range in which you want to fill in consecutive numbers automatically. Release the mouse button and the Ctrl key. Now, your consecutive numbers will be automatically filled in. |
| Ever wonder how to get rid of grid lines in Excel? | Open the Excel document you need to modify. Highlight or choose the cells you want to remove the faint grid lines from. Choose the Fill Color icon on the Formatting toolbar. Click the dropdown arrow and choose white (bottom right corner). If you need to draw in border lines, just click in the area and corner points will show you where the cell borders are. |
| Need a quick way to find a sum or average in Excel? | Want to see the sum, min, max, count, or average of a range of cells in a spreadsheet without entering a formula? Here's an easy way: Right click anywhere on the Status bar (bottom of Excel window) and select the quick function that suits your need. Now simply highlight the numbers you would like the quick function to be applied to and voila! Your result appears at the bottom right of the screen. |
| Want to view and print all formulas in a spreadsheet? | Sometimes you might need to look at the formulas behind the values they produce. It's easy in Excel. Here's how: Press Ctrl + tilde (~) to view the Formula Auditing Toolbar. Pressing Ctrl + tilde (~) again returns you to the normal view. |
| Need to display units (ft., in., lbs.) in a spreadsheet? | Select the range of cells that you would like to display. Go to the Format menu and select Cells. In the next dialog box, go to the Number tab and click on the Number category. Specify the format that you'd like for the numbers. Click OK Bottom. |
| Want to undo more than just the last action you performed in Excel? | If you realize the last several items you've entered are incorrect, you can undo the entire series of items. Here's how: Click the arrow next to the Undo button in the toolbar. Select the number of actions from the list. Microsoft Excel reverses the selected action and all actions above it. |
| Ever want to limit which values can be entered into a cell in Excel? | Want to limit the type or range of values that can be entered into a cell? Highlight the appropriate cell(s). Click Data - Validation. In the "Settings" tab, choose any validation criteria you wish to apply (examples: 1-5, A-B-C,etc. ). From the Data Validation menu, you can also create a custom Input Message that informs the user of the valid data types, as well as a custom Error Alert message. |
| Ever want to hide a range or group of data within your spreadsheet, without deleting it? Try the GROUP command. | Using the GROUP command: Highlight the column(s) or row(s) you want to group. Select Data => Group and Outline => Group. This will mark the columns or rows with a line that ends with a +/- button that can be clicked to collapse or expand the group. When the group is expanded you can click anywhere on the line to collapse it. |
| Ever need an easy way to insert a check mark into Excel? | From the font dropdown in Excel, choose wingdings2 font and type a capital P. |
| Need to add a worksheet in your Excel file? | Excel allows up to 99 separate worksheets for every single Excel file. From within your Excel file go to Insert => Worksheet. Or simply right-click on the excel tabs at the bottom of the spreadsheet and select INSERT. |
| Ever need to convert text in Excel from all caps to sentence case? | Sometimes you might have data (TOM JONES) that is displayed as all caps but which needs to be in lower case ( Tom Jones). To make this change: Navigate to an empty cell in your worksheet. If you're planning to convert an entire column, it's best to find an empty column. Assuming the text you wish to convert is in cell A1, enter the following formula into the empty cell: =proper(A1) Copy and paste this formula into as many cells as you need converted. |
| Ever wanted to reference your spreadsheet's tab name in a cell? | Here's the formula to automatically get the tab name: Formula reads "=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)" Note: You must save the spreadsheet for this to work; otherwise the cell returns "#VALUE!". |
| Need to lock rows and columns in Excel? | Sometimes you might need to compare fields in Excel that aren't normally adjacent to each other. Fortunately, you can lock rows and columns so that when you scroll through a spreadsheet, column and row headings stay in place for easier viewing. Choose Window => Split. Drag the bars to the appropriate spot. Choose Window => Freeze Panes. |
