“Most essential tricks for free – Excel Worksheets”

Excel worksheets; if you know the usage or basic ways as how excel worksheets can help you in your daily personal or professional life

There are different things which any one can do in MS excel worksheets, from which we would learn Top tricks to make excel worksheets from this article

MS excel can be used for many works, for example:

• Making list of items with rates and then sorting them in some random order
• Documenting items with filters to have proper usage and retrieval method
• Making excel automated sheets to add, retrieve or document data in customized order
• Making quality sheet for call centers(BPO), monitoring defects (manufacturing) & maintaining improvement data sheets in quality
• Inventories of items, products &accessories

Let’s Start Excel:

Irrespective of which version of MS Excel you are using, more or less the headings and items in them are same, approximately. It is you who have to figure out which option is there in which version, and it is quiet useful for you to know as it is beneficial for longer run

• Out of all works mentioned above, we would pick the 4^th option as working on that option would yield learning for all of them
• We would using following in order as required
  • Borders
  • Wrap Text
  • Merge & Center
  • Orientation
  • Conditional Formatting

You can locate all these functions under “HOME” button in MS Excel. Just try to locate them and customize them in handy location so to use them more often

• We would use “INSERT” option also as it would give us – pivot, table, charts, header and footer-Text
•  Use “Option” view and unclick GRID LINES after which all lines in excel would disappear. This can be used to make sheet more re-presentable
• Try and do some “Trial and Error” work for above mentioned options and get familiar to make some good presentable format
• Just below the sheet at bottom line, there are sheets displayed – SHEET 1, SHEET 2, and SHEET 3; there are lots of things which can be done with these sheets. For example:
  • Right Click on SHEET 1 – Insert, Delete, Rename, Move or Copy, Protect Sheet, Hide & Select all Sheets
  • Insert – you can insert sheet into the existing worksheet
  • Delete – deletes that sheet which you selected as “ right click”
  • Rename – You can rename from Sheet 1 to any name you want
  • Move or Copy – In this option, you can shift this sheet to any other sheet which is open along with this sheet. In second option, in “before sheet”, you can move the existing selected sheet to any other sheet which is selected in the above option. Also, you can also create a copy the same “sheet 1” and move it outside of this MS Excel worksheet
  • Protect Sheet – By this option, you can protect this sheet with password so that it is used by any person whom you authorize
  • Hide – By this option, you can hide this existing ”sheet 1” so that it is not visible to anyone
  • Select all Sheets – By this option, you can select all the sheets which are there in this worksheet in which you are working on
• Usage of  if Loop and how it is used –  those of you who are not aware of this formulae, please read this carefully:
  • In this formulae, we use the formulae which is =if( parameter 1, parameter 2, parameter 3); this means if you are to satisfy some condition in where you want some decisions like if Cell 1 has value 2, then cell 2, should display value 4 otherwise display “no”. Now if we have to use this condition in if Loop, then it would look like this:

=if (cell 1=2, 4,”No”) (when we display text in loop, we used this format)

This formulae can be used in many areas and it gives more benefit to users who want to create excel with good functionality. Also, if look can be used 7 times in same loop i.e. second parameter can be extended to 7 times and it is called nested IF LOOP

• Now, using charts:  Best way to use excel chart, is select the values for which you want to create chart, and then select any chart, it would automatically display the same for you (Option “INSERT”  – chart )
• Now coming to how to use excel worksheet in case we wish to use any RANDOM FORMULAE: Imagining we have data in two columns i.e. Column A & Column B. Data is as follows; A has names and B has total salary earned per month. Now if we have to add all of them or take average of the same, we can use it this way. Just press equal to and then randomly the formulae comes from where we can select the choice. Now, if we have to use SUM formulae, the option comes as number 1, number 2 i.e. which means we can select either cell or cells to calculate the same. There is nothing to learn in this as by using this method, any one can know by “hit” or “trial” method as how to use effectively this formulae
• Please also check these formulae to learn –
  • TIME(hour,minute,second)
  • TIME(time_text)
  • NOW()
  • SUMIF (range,criteria,sum_range)
  • SQRT (time)
  • ISERROR (value)
  • VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
  • CONCATENATE(text 1, text 2)
  • MAX(number 1, number 2)
  • MIN(number 1, number 2)
  • CORREL (array 1, array 2)
  • TEXT(value, format text)
  • TRIM(text)
  • CHAR(number)
  • CLEAN(text) , all these formulae do add value to your data representation or the way it is being used further. Know this as it would add that KICK into your work

---

Some more tips:

• Use Shift + Tab ( it moves your cursor upwards in Excel)
• Use Shift + F3 on selected text ( It changes font size for you in MS Word)
• Use Shift + Space Bar ( selects whole row for you to work on without using mouse)
• Use Ctrl + Space Bar ( selects whole column for you to work on)
• Use Right click on Sheet tab on bottom on excel sheet, it gives you an option of Move/Copy – to Book ( by this you can copy excel sheet of other excel opened on computer without opening it or you can make a copy of your excel sheet on other worksheet – try it)
• Use formulae Rand()- it generates random number for you to work on, also if you wish to have more random number, drag the bottom right corner of that excel selected till what number you want)

Shortcuts:

• To add: select the top cell of row – press alt + enter ( it would add the row automatically without putting sum formulae)
• Select all in a row/column: Shift Ctrl Arrow down
• Go to Last cell on a column/row: Ctrl Arrow Down
• Lock Sheet: Tab REVIEW – PROTECT SHEET
• Reduce Excel sheet Size: CTRL Mouse Roller up & Down

Check out this post for more information on:

• Creating Excel Monitoring Sheet – daily observation values,daily six sigma correlation values, daily productivity scores etc
• Creating Simple Excel document for house hold expenditures monthly/quarterly/six monthly