music equalizer
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: Excel tips

  1. #1
    Member
    Join Date
    Jul 2013
    Location
    Santiago
    Posts
    66

    Excel tips

    Lets start a post for Excel tips.

    One trick I learned recently is a function called TRIM.
    If there is unwanted whitespace around text in a cell, you can remove it with TRIM.

    For example, if the cell in A1 is "

    Someone's Name " you might need clean it up to show "Someone's Name".

    You can type =TRIM(A1) into A2, and it will display "Someone's Name".

    If you want to replace A1 with this, you can copy A2 and then right-click on A1 and select 'Paste values' (it won't work if you just paste normally).

  2. #2
    Junior Member
    Join Date
    Jul 2013
    Posts
    25
    Great idea for a thread.
    Excel can be frustrating if you're not used to using it - it's not always obvious how to do even simple things.
    One thing that annoyed me when I started using it was that pressing Enter doesn't create a new line.

    So my tip for new users is how to start a new line in the same cell.

    It's simple:
    Alt+Enter

  3. #3
    Member
    Join Date
    Jul 2013
    Location
    Santiago
    Posts
    66
    That's a good tip. Did you know you can stop Excel automatically going to the cell below when you press Enter? You can change it to go above, right or left instead.
    Here's how:
    1. Click on File
    2. Select 'Options'
    3. Choose 'Advanced options'
    4. Directly under the heading 'Editing options' you will see a drop-down menu where you can change what happens when you press Enter.

  4. #4
    Senior Member
    Join Date
    Jul 2013
    Posts
    229
    How to convert an excel column into a list for programming
    If you are a programmer, you may sometimes want to create a list like this ['item 1', 'item 2', 'item 3'] out of an excel column like this:
    item1
    item2
    item3
    Here's how to do this:
    Imagine you want to create a list from everything in the first column.
    In the second column (or any other column), write =""""&A1&""","
    This will create a cell where the text is in quotes followed by a comma.
    Then you want to drag this cell down for every cell in your column.
    Now copy the column that has the quotation marks and commas.
    Next, paste this into a text editing program such as Notepad ++, and applying the 'unwrap' function to get the text in one long line.
    Finally, put an initial [ at the start, remove the final comma and put a ] at the end.
    Now you have a functioning list for languages such as Python.
    Last edited by laurence; 08-06-2013 at 12:42 PM.

  5. #5
    Junior Member
    Join Date
    Jul 2013
    Posts
    25
    Calculate the number of unique items in a row or column

    Sometimes you might have lot of names (for example) in a column, and you might want to know how many unique names there are.
    Another way of putting this is you might want to count cells excluding duplicates.

    I found a formula that does this. It looks quite complicated, but it works.
    Here's an example:
    Imagine you have a column of names from A2 down to A500. You want a cell that shows how many unique names there are in this column.
    The formula would be as follows
    =SUM(IF(FREQUENCY(MATCH(A2:A500,A2:A500,0),MATCH(A 2:A500,A2:A500,0))>0,1,0))

  6. #6
    Senior Member
    Join Date
    Jul 2013
    Posts
    229
    Timestamp a spreadsheet entry
    In Excel, you can use =NOW() or =TODAY() to show the time or date respectively. The problem with this is that when you reopen the document, the time and date will be updated. If you want to 'timestamp' something, and keep the time and date fixed, you should use the following formula:
    Ctrl+Shift+; for the time
    Ctrl+; for the date

  7. #7
    Member
    Join Date
    Jul 2013
    Location
    Santiago
    Posts
    66
    Write in all sheets at once
    If you have an excel file with multiple sheets and you want to write in the same cell in all sheets at once, just do the following:
    Select the cell in any sheet.
    Hold down Ctrl, and click on every sheet at the bottom of the screen.
    Then type what you like in the cell, and it will appear in every sheet.

  8. #8
    Junior Member
    Join Date
    Jul 2013
    Posts
    25
    Drag a formula while keeping some cells the same
    One undesired consequence of dragging a formula down into other cells is that some of the values we want to stay constant will change.
    Avoiding this is simple: just place $ before the column letter and row number you want to keep constant.
    For example:
    =A2*$B$3
    If you drag this down you will get A2*B3, A3*B3 and so on.

  9. #9
    Senior Member
    Join Date
    Jul 2013
    Posts
    229

    VLOOKUP Example

    VLOOKUP is a very useful function, but it can be difficult to grasp at first. VLOOKUP lets you find a match to a cell, and then return the value in a cell that is associated with it.
    For example, imagine you have a long list on customer names in the A column, and their unique customer numbers are in the B column. You want to be able to input a name and have a formula return that customer’s customer number.
    Here’s an example with just a few customers.
    In A2:A6 we have the customer names, and their customer numbers are in B2:B6. This is the information our formula will be looking in. Taken together, this data is A2:B6.


    We are going to input the customer name in D2. We want the formula to take this, find a match in A2:B6, and then return the value of the cell in the second column – the customer number.
    If we want to show the matching customer number in cell E2, here’s what our formula will look like:
    =VLOOKUP(D2, A2:B6, 2, FALSE)

    The D2 is the cell we are looking to match.
    The A2:B6 is the area we are looking in.
    The ‘2’ means return the value in the second column.
    The FALSE means we are looking for the first exact match.
    Last edited by laurence; 09-02-2013 at 08:51 AM.

  10. #10

    Format a cell so users can only input certain values or values within a range

    Errors in data entry can create problems. One solution to this problem is to format cells so that they will only take specific values from a list, or values within a given range.
    Here’s how:
    1. Select the cell you want to format.
    2. Click on ‘Data’, and choose ‘Data Validation’. The Data Validation box will appear.
    3. Under ‘Settings’, you can choose which data type you want to allow, and also specify a range. If you choose ‘List’, you can either enter a list which can contain any data type, or chose a range of cells from within the worksheet. If you choose ‘List’ the user will have a drop-down menu to choose from when inputting data into the cell.
      While still in the Data Validation box, you can also add an input message and change the error alert to make it more specific and helpful your users.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •