Browsing the Excel category

Excel REF error

I recently received an email:

i’m trying to combine 2 files i received and everytime i delete a row i don’t need it messes up the calculations. what did you do that time to keep the numbers and not have them change to REF…….?

The data happened to be filtered, and there were some calculations; and my coworker had issues.   The solution was to copy and paste special >> values.  Doing this preserves the “values”, and even if you delete other rows/columns (that a formula would have relied upon), the values will remain intact.

Read the article

excel convert minutes to fractions of hour

I needed to convert the amount of time (in hours/minutes) to a fraction.  For example, the time between 8:00AM and 9:30AM would be “1.5 hours”.

 In Excel, I specified “8:30 AM” and it was formatted to 8:30:00 AM (so I knew it picked up the right format).  You can subtract the two times easy enough; the trick is to then multiply that amount by 24.

So if you have

A1 = “8:30 AM

B1 = “5:00 PM

Then C1 = “=(B2-A2)*24”

… but be sure to format that as a number.  You’ll get “8.5”

Read the article

excel 2013 open blank file instead of template

When I open MS Excel, I want a blank template to come up.  When I received a new computer at work, I kept getting prompted to specify the “template” to use; and I had to manually select “blank template”.

 You can default this by going to Options, then uncheck the box for “Show the Start screen when this application starts”

Read the article

Excel copy down values without overwriting other values

I didn’t know how to title this blog post; it’s a neat trick,but I can’t even really describe what it does!

Let’s say you blank rows that you want to fill in the value above it; but you’ll need a formula to do so, as you don’t want to over-write the value of the new row.

You want to turn this:

excel_copy_values_before

Into this:

excel_copy_values_after

 

The trick is to get the values you need in another column (ex, “C”), and then move it into the desired Column (“A”).

And here’s how:

  1. First make C2 equal to A2:

excel_copy_values_step1

2.  In C3, put in: =IF(A3<>C2,IF(A3=””,C2,A3),C2)

excel_copy_values_step2

… we’re seeing if the value of  that cell is NOT the cell above it; if it is, we put in one value – otherwise, another.

3. Copy that formula down; then copy/paste-special values over the original!

excel_copy_values_step3

Read the article

excel where to find circular reference

MS Excel >> “Formula tab” >> “Formula Auditing” section >> “Error Checking” – click the little arrow next too it.

Read the article

Unusually large Excel files (in MB/KB)

I had an Excel file that was unusually large in file size.  Turns out I copied extra rows from a Pivot Table I had made when pasting it onto a new sheet.

Solution: delete the blank rows at the bottom of the worksheet (Windows keyboard shortcut: go to the first blank row, hold SHIFT, hit “end” and then “down” (press/release end + down, while still holding SHIFT.  That will highlight the data.  Right click >> Delete).

Read the article

How to make a shared file in excel, unshared?

Someone asked me that; it’s the reverse of “sharing” the file:

 

tab “Review” >> Share Workbook >> editing tab, uncheck “allow changes…”

Read the article

Open Excel on 2 screens

I have 2 computer monitors, and I needed an instance of Excel opened (separately) on each screen.

The trick is to open Excel as normal… then (assuming you’re in Windows), right click on the Excel icon in the start bar.  Hold “Shift” and select “Microsoft Excel [2013]”.  Excel will open in a separate window that you can move to the alternate monitor.

Read the article

Excel subtotals within subtotals

Sometimes you need a subtotal in Excel, inside of another subtotal.

First, do a regular subtotal.

NOTE: whenever you do subtotals, make sure your data is sorted first!

Highlight the data

Go to the “Data” ribbon

Click on “Subtotal”

Press “OK” after you select the “each change in”, which function to use, and what to apply the function to.

TIP: if you have a lot of items to add the subtotal to, the keyboard shortcut is “space bar” – so alternate press the up/down arrow keys and then “space”

Now you have a (regular) subtotal.

Click the same “subtotal” button again, but this time UNCHECK the first checkbox “replace current subtotals”.  Then press “OK”.

BONUS: I tend to view “Subtotal View 3”, then Go To Special >> Visible Cells Only.  Change the background color.  Then view  “Subtotal View 2” and repeat but with a different color.  Now when you expand the data set, the color coding easily distinguishes the totals.

excel-subtotals-within-subtotals

Read the article

Get data from SQL Server in Excel

You can use this VBA code to query a SQL Server database, right from Excel:

 

https://gist.github.com/stevesohcot/47d73642ff0c9475d99e89d0a2d141d1

Read the article