Imagine you have a huge spreadsheet of entries and you need to check to make sure that there are no repeated records. Now, you can go through each entry and manually check, but visualize checking 10,000records for this. Time consuming, isn’t it? Fortunately, Excel comes with a ‘Remove Duplicates’ feature.
Select a range of data, click the Data tab and then click Remove Duplicates.This will bring up a new window in which you can specify the columns in which Excel would check. After clicking OK, Excel will then remove all duplicate records.
If you want to select range of cells – without using Mouse selection Or Shift key, you can use F8 key as briefed below :-
Suppose you wish to select the range A1 to D3. Select cell A1 and press F8 key. Now this will freeze the A1 cell and now select the cell D3, you will find that the range A1:D3 gets selected automatically.
You can use this technique to define a named range for the selection A1:D3 or to format them.
Use a Macro to Add Multiple Sheets
To add multiple sheets, use a simple macro. In this example, I want a tab for each week of the year. The default is three sheets in Excel, so we need 49 more. Select the Developer tab, select Macros. In the Macro Name box, enter Add_49_Sheets and select Create. In the VB Editor, you’ll see
On the next line paste:
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=49
This line should be followed by
Now close the VB Editor, select Macros, select Add_49_Sheets, and select Run. You should now have 52 worksheets. To change the number of sheets, simple replace the 49 (Count:=49) with the number of required worksheets.
Freeze Panes to See Headings When Working with Multiple Columns and Rows
When working with multiple rows and columns, you may want to see your column and row headings. To freeze a single row or column, go to the View tab, select Freeze Panes. To free only the top row, select Top Row or to freeze only the first column, select First Column. In this case, I wanted to see both by first row and column, so I selected the entire first row. Holding control, select the entire first column, then go to Freeze Panes, and select Freeze Panes. Go to back to the View tab, select Freeze Panes and then Unfreeze Panes to unfreeze.
Visualize data trends with Sparklines
Sparklines allow quick graphical representation of data with a couple of mouse clicks, fitting a graph within a single specified cell. Select the Insert tab, above Sparklines, select the style of graph – column, bar, or win/loss. Chart options, such as color may then be further specified.
Filter and Sort Data in Excel
Select the cells to be filtered and sorted, select the Data tab, select Filter. Use the dropdowns at the top of each column to sort alphabetically, numerically, or manually check/ uncheck desired values.
Use Text to Columns to Separate Text (for example First and Last Names)
Select the column or cells containing data to be separated, select the Data tab, select Text to Columns, select delimited (in this case because we will use the spaces to separate), select Space, select Next, select the first cell in which the new text should begin (destination), click Finish.
Locking Down Cells in Excel
To lock cells containing formulas in excel. Select all cells within a sheet and select Format Cells . Go to the Protection tab and unselect locked and then Okay. Next, select the cells you wish to protect, go back to format cells. Now select “Locked.” To apply the changes, select File, Protect Workbook, Protect current sheet. Enter a password and ensure there is only a checkmark on “allow select unlocked cells,” then select Okay. Reenter your password.
Use Groups to Tie a Range of Cells Together so they can be Expanded/Collapsed
When working with large sets of data, improve your workflow by grouping cells. Select the rows or columns you wish to group, select the Data tab, select Group, then select either rows or columns. Use the + or – symbol on the left to expand and collapse groups. You can also create subgroups of data as well as determine subtotals.
Have you ever used groups - Share the applications where you can use the same or already using