Browsing the Excel category

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

excel display double quote in formula

Put the double quotes IN double quotes:

” ” ” (but without the spaces)

Alternatively, you could put the double quotes in a cell, and then reference that cell

Read the article

MS Access values truncated when running multiple queries

Something I do often is I’ll run a make-table query, then run an append-query (to the table just made), then run a third query off of that combined table.  I ran into a problem though where the summed values was not what I was expecting.

In the first query, the numeric values were whole numbers.  When it made the table, the data type a (long) integer.  Then when the append query ran – even though the values were decimal/double (with decimal points), the data was truncated (rounded?) into the data type integer field.  My first attempt was to just change the data type of the first query by using the CDBL() function, but that didn’t work.

The solution I went with was swapping the order of the first two queries; so that when the table was created, it had a data type that supported decimal places.

Read the article

1 + 1 + 1 = 2

A coworker had an Excel file where 1 + 1 + 1 = 2 .  The problem was that it was rounding values to the nearest whole number.  The true values were 1 + 0.5 + 0.5

Read the article

#N/A’s there: no sum

A coworker had a column of data in Excel, where the first row had the summation of the rows below it.  When she copied it over to the adjacent column, it didn’t work.  The problem was that there were some “#N/A” ‘s in the data; and those cannot be summed.

Read the article