Skip to main content

Posts

Showing posts with the label excel

Can't scroll using arrows on excel

Usually, I use my arrow keys to navigate through my excel but lately, they (arrow keys) haven't been really helpful. Like at all! The issue was I had the scroll lock "on". But I am on Windows 10 and I can't see the scroll lock function key anywhere :O Here is how you can turn off the scroll key on Windows 10: Press the  Windows key . Type  onscreen keyboard  and press  Enter . When the onscreen keyboard appears, your ScrLk (Or scroll lock button) will be highlighted which means it was turned on. Click the  ScrLk  button to turn it off. Go back to your excel and you should now be able to use your excel.  Hope this helps!
Post a Comment
Read more

MS Excel - Find Duplicates in a Coloumn

Lets say you have a column full of data in your excel and you want to find out the duplicates in it: Method 1 - Using Countif function Let's say your data is this Column A Siri Sekhar Sahan Sahiti Sahana Siri Sekhar Sekhar Now in Column B enter the formula in the first cell =CountIF($A$1:$A$8, A1) Then sort the data by Column B and you will know which data is repeated Method 2 - Using Pivot Table In Excel 2013, go to Insert --> Pivot Table When asked to select Table or Range, select the Column A data Now when the pivot table fields are shown select the Column A and drag it to "Row Labels" and "Values" -  Set "Values" to Count This will give you the count of each of the element thus enabling you to find the duplicates.
Post a Comment
Read more

Looking up for an example on VLOOKUP?

From all the questions I have been asked about and I have asked mostly are related to Excel Formulas If I filter even further I will find that I am looking for information on vlookup most of the times… Here is one attempt from my end to explain what vlookup is all about. What’s VLOOKUP? Ya ya apart from the obvious answer that its an excel formula, it’s a search function! It can find you matching information from a particular table of data – something similar to a hashtable or dictionary in data structures   You give it an identifier and it will find the corresponding value for it…   Can I get a VLOOKUP Example? Of course! That’s what this blog is about   Jokes apart, here is an example of VLOOKUP Problem Statement: Your boss asks you to find out the work experience in number of years of your team mates Data you have: Now you have to fill in this table to present it to your boss: Now you’ll get the data in columns H, I from C and D – isn’t it? that’s simpl...
Post a Comment
Read more

Circular Reference Warning/Error message

In Excel when you refer the cell where the formula lies in, it is a circular reference as you get the workbook confused... For Ex: You are writing a formula in A1 and the formula you enter is =Sum(A1:A5) this is a circular reference as you have the formula in A1 and you want the sum to include A1 Now the frustrating part in the circular reference error message is that it doesn't point you to the location which has the error. Thankfully there is an easy way to figure out which cells have circular reference: To locate circular references: Go to Formulas --> Error Checking --> Circular Reference (Excel 2010) Once you hover over it, you will see all the cells which have circular references...you can then rectify the errors... there are resources which explain in good detail: Video or in excel help go to "Excel 2010 Home > Excel 2010 Help and How-to > Formulas > Correcting formulas"
Post a Comment
Read more