View SpicyFlavours Stats
Jump to content
Positive Zeal
Sign in to follow this  
  • entries
    10
  • comments
    0
  • views
    2,497

About this blog

I love to analyze data using MS Excel Spreadsheets

Entries in this blog

 

Automatically SUM() with ALT + =

Quickly add an entire column or row by clicking in the first empty cell in the column. Then enter ALT + ‘=' (equals key) to add up the numbers in every cell above.

saurabhjain

saurabhjain

 

Remove repeated entries easily in Excel

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.    

saurabhjain

saurabhjain

 

Selecting range using F8 Key - without using mouse

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.

saurabhjain

saurabhjain

 

Use a Macro to Add Multiple Sheets

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 Sub Add_49_Sheets() On the next line paste: Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=49 This line should be followed by End Sub 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.

saurabhjain

saurabhjain

 

Freeze Panes to See Headings When Working with Multiple Columns and Rows

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.

saurabhjain

saurabhjain

 

Visualize data trends with Sparklines

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.

saurabhjain

saurabhjain

 

Filter and Sort Data in Excel

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.

saurabhjain

saurabhjain

 

Use Text to Columns to Separate Text (for example First and Last Names)

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.

saurabhjain

saurabhjain

 

Locking Down Cells in Excel

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.

saurabhjain

saurabhjain

 

Use Groups to Tie a Range of Cells Together so they can be Expanded/Collapsed

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 Regards Saurabh

saurabhjain

saurabhjain

Sign in to follow this  
×