Excel tips
Freeze Row and Column Headings
This is the simple but useful trick to becoming an Excel expert. Freeze the row and column headings so they are always viewable when you scroll around. To do so, LOOK Place the cursor in the top-left cell where the actual data starts and go to VIEW menu and simply select Freeze Panes and Freeze Panes. Now the heading is viewable where ever you scroll.
Hide Individual Cells
Excel has a master trick of hiding cells. This is one of the best Excel tips and tricks All you need to do is to select the cell that you want to hide and right click >select Format Cells and then set the format as Custom under the Number tab and Enter;;; (three semicolons) as the format. Now the cell content disappears but they are still present there and can be used in formulas. This is really amazing Excel tips and tricks
Turn Columns into Rows
If you have data in columns that should be in rows or the other way then here we have tips to do so without losing anything. Just, copy the original block of the cell > right click on the destination cell > click Paste Special > Transpose.
Apply Diagonal Borders
If you got a table that requires both row and columns headers in the same cell then make use of the diagonal borders. Click More Borders at the bottom of the borders drop-down menu (on the Home tab of the ribbon) and diagonal buttons are by the box corners. Click it and save. This makes the work easily understandable and presentable in your excel sheet. Additional Reading:
- 11 Vital Tips for Optimizing Excel Spreadsheets and Speed-up your Excel
- 11 Simple Yet Powerful Excel Troubleshooting Tricks for Analyzing Data
Add More Than One New Row or Column
Well, I think most of the Excel user must know that by selecting a row or column we can add a new one via Insert drop-down under Home. However, here is an easy way to do so. Drag and select rows or columns > right click the highlighted rows or columns > select Insert from the drop down menu. Now new rows will be inserted above the row or to the left of the column you first selected. NEXT…
Add Your Own Graphics to Charts
While making charts graphics you don’t need to settle for the colored blocks that by default Excel offers. All you need to double-click on a bar and then click paint bucket to change the fill options. You can also switch to gradients, a pattern, or load in an image file from disk.
Learn Excel’s Best Shortcut Keys
Well, this is the last tips and tricks of our article and this is very helpful for the Microsoft Excel users to fix the error. You should learn some shortcut keys to increase your productivity in Excel. Here we have included some shortcut keys that are useful for regular work.
- Ctrl +[Down|Up Arrow Key]: This is used to moves to the top or bottom cell of the current column and using Ctrl with Left|Right Arrow key, moves to the cell furthermost left or right in the current row
- Shift + Ctrl + Down/Up Arrow: It will select entire cells above or below the current cell
- Ctrl+ Home: Go to cell A1
- Ctrl+End: To go to the last cell that contains data
- Alt+F1: It will create a chart based on selected data set.
- Ctrl+Shift+L: This will activate an auto filter to the data table
- Alt+Down Arrow: For opening the drop down menu of the auto filter. To use this shortcut:
- Alt+D+S: For sorting the set data
- Ctrl+O: Open new workbook
- Ctrl+N: Create a new workbook
- F4: To select the range and press F4 key, this will change the reference to absolute, mixed and relative.
Trace precedent/dependent formulas
Both functions only work on the active cell selected so, unfortunately, it's one cell at a time. Click the Trace Precedents or Trace Dependents button in the Formula Auditing group of the Formulas tab to generate the blue arrows. Data flow follows the direction of the arrow with the blue dot being the precedent and the arrow being the dependent. Below, Trace Dependents for cell E2 shows it only flows to cell G2. And Trace Precedents for cell I4 shows cells E2 and F2 are the only cells flowing to it. These functions work across tabs of the same workbook and different workbooks with one exception. Trace Dependents won’t work for external links for workbooks unless they are open.
Group/ungroup columns to hide detail data
Spreadsheets with a bunch of complicated and detailed information can be hard to read and analyze. Luckily, Excel provides an easy way to collapse and expand the messy details to create a more compact and legible view. Grouping in Excel works best for structured worksheets that have column headings, no blank rows or columns, and data is sorted by at least one column. Select all the data you want to summarize in the group. Then, go to the Data tab > Subtotal. This will bring up a pop-up in which you can select how the data should be grouped and summarized. In the example below, we grouped by the change in OrderYear and summed on Total. This will show us total sales for each year and in total. Here is the result: Now with just a few clicks, you can go from meaningful summaries to fine detail on the same spreadsheet.
Protect sheets and workbook
Once you click Ok, you are prompted to confirm the password and save the workbook. Now, if anyone tries to alter the information they will need that password. If you have a bunch of sheets you want protected, click on Protect Workbook follow the same procedure.
