Just the other day, I opened up Excel and started typing in some numbers to add up.
18546
2523485
36500
Then I looked at my screen, and Excel is showing this:
What's up with that? It looked like my numbers were getting divided by 100. Then I realized it was adding a decimal to the last two digits, like using a ten-key!
I quickly found the answer, which lies in Excel's advanced settings.
Go to File > Options > Advanced
Uncheck the box "Automatically insert a decimal point", and you're done!
Boom!
Tech Tip Tuesday
Tuesday, January 17, 2017
Tuesday, November 1, 2016
If This Then That
IF THEN
Have you ever found yourself doing something that you feel like someone or something else should be doing for you and go "damn, I really wish my computer would do this for me!"? Well me too. And so did the folks who made If This Then That (IFTTT for short).
IFTTT is a very cool website that allows you to create custom "recipes" to basically automate things for you without you having to learn any programming languages. The way it works is you connect your existing accounts or sign up for new accounts for things they call "channels", like Gmail, Google Calendar, Nest, FitBit, Craigslist, Twitter... basically any software or smart device that they've hooked up with (there are currently 366 different channels, and they're adding more all the time), and you can create a "recipe" that will do something for you when something happens. They have tons of pre-made recipes up there already you can start with. If you create your own recipe, you can publish it!
Here are some examples:
Text me when a VIP sends me an email
IF my gmail receives an email from a specific sender
THEN send me a text message to alert me about it
Farm Tractor Tweets
IF an Ebay search finds a new posting for a used tractor for sale,
THEN send a tweet about it along with a photo on twitter!
Receive an email if there will be rain in your area tomorrow
IF the weather forecast shows rain is predicted in your area,
THEN send an email to you letting you know
If the temp rises over 99F, email the president about global warming
IF the current weather goes over 99 degrees Fahrenheit,
THEN send an email to the president about global warming
There are tons of other great recipes (including some that are actually useful) so I encourage you to explore and see if you can find any that you might use.
Cheers!
Daniel
Thursday, July 21, 2016
Python!
Howdy everyone! It's been too long, sorry about that.
So I've been obsessing lately with Python, and wanted to share my newfound respect and obsession. In fact, I actually created a blog completely devoted to Python, which will focus on its use for CPAs and accountants. I haven't actually started posting on that blog yet, but I have some plans for it.
If you have never heard of Python, or are familiar with it but haven't used it much, it's awesome and I encourage checking it out. There are some great free resources online to learn from, including several great courses on Coursera. My favorite is the Rice University series.
Afraid of Python because you're not a programmer? No fear! It uses mostly plain English and simple statements. Of course it can get massively complex, and looking at new code and libraries and all of the different options for writing and using it can get daunting, but if you start small and basic, you'll quickly learn how powerful it is.
Why would you care about Python? Because it's MEGA POWERFUL. I used to think Vlookups, Sumifs, and Pivot Tables were the next level in Excel. Visual Basic definitely is up there in the next level category in excel of data analysis and performing many calculations. BUT in the Excel world, there are limits. And there's a lot of room for errors. With something like Python, especially Pandas (more on that later, in the python blog), you can have HUGE amounts of data, and perform analysis and calcluations much more efficiently, accurately, and consistently.
I'll keep most of the Python specific things on that blog, however if there's something I add on this blog that can also be done in Python, I'll mention it on here.
Check it out!
Cheers,
Daniel
So I've been obsessing lately with Python, and wanted to share my newfound respect and obsession. In fact, I actually created a blog completely devoted to Python, which will focus on its use for CPAs and accountants. I haven't actually started posting on that blog yet, but I have some plans for it.
If you have never heard of Python, or are familiar with it but haven't used it much, it's awesome and I encourage checking it out. There are some great free resources online to learn from, including several great courses on Coursera. My favorite is the Rice University series.
Afraid of Python because you're not a programmer? No fear! It uses mostly plain English and simple statements. Of course it can get massively complex, and looking at new code and libraries and all of the different options for writing and using it can get daunting, but if you start small and basic, you'll quickly learn how powerful it is.
Why would you care about Python? Because it's MEGA POWERFUL. I used to think Vlookups, Sumifs, and Pivot Tables were the next level in Excel. Visual Basic definitely is up there in the next level category in excel of data analysis and performing many calculations. BUT in the Excel world, there are limits. And there's a lot of room for errors. With something like Python, especially Pandas (more on that later, in the python blog), you can have HUGE amounts of data, and perform analysis and calcluations much more efficiently, accurately, and consistently.
I'll keep most of the Python specific things on that blog, however if there's something I add on this blog that can also be done in Python, I'll mention it on here.
Check it out!
Cheers,
Daniel
Tuesday, December 22, 2015
Tech Tip Tuesday - SUMIF, SUMIFS, and Pivot Tables, oh my!
Long time no see!
Five Different ways to add:
Breakdown:
Ten-key:
Equals and Plus formula.
Flag and a Sumif formula:
SUMIFS formula:
Pivot Tables
Cheers!
Hey everyone,
HAPPY TUESDAY!!!!!!
I wanted to share some useful ways to add cells in excel
that can increase efficiency in any kind of analysis or summing type situation.
I’m providing this example for demonstration purposes, but
you can apply these techniques to a number of different scenarios in excel. The
SUMIF and SUMIFS formulas can be used with more types of data than a Pivot
Table, however if the data’s in the right format, Pivot Tables are much more
versatile and powerful than the SUMIF and SUMIFS formulas.
Example: take a list of numbers that we want to summarize in
a certain way. Here I have a list of names, dates, numbers, and type of animal.
Let’s say this is a list of how many cats or dogs each person adopted on each
day.
Now, if we want to add up the total number of cats and total
number of dogs that Sally adopted during this time period, there are a few ways
we can do this:
Five Different ways to add:
1.
Ten-key / adding in the cell
2.
Using an Equals and Plus cell reference formula
3.
Adding a flag and using a Sumif formula
4.
Using a Sumifs formula
5.
Using a pivot table
Breakdown:
Ten-key:
a. How: use the ten-key on your desk, a
calculator app on your computer, or add within the cell (6+5+1+8 = 20)
b. Analysis: While this is a reliable and
tried & true method, it’s arguably the least efficient, especially when you
start getting past 50 items or so (depending on how speedy you are with the
ten-key)
Equals and Plus formula.
a. How: use a formula in another cell. In
the above example, the formula for Sally’s Cats would be “=C11+C17+C19+C29”
b. Analysis: While this seems easy, it’s
still a lot of manual work, and almost as inefficient as using the ten-key.
Also if you miss one, it’s not very easy to tell which cells you might have
missed, again especially if you have over 50 rows of data. Also this one is a
little too “clicky typey” for me.
Flag and a Sumif formula:
a. How: Add a “flag” such as a number or
character in the row to the right of the numbers you want to add. For example,
see below:
§ The
SUMIF formula has three parts*: (range, criteria,
[sum range])
§ The
“range” tells the formula which cells to look for the matching criteria.
§ The
“criteria” tells the formula what to look for within the range.
§ The
“sum range” is the range we want to add if the “range” cell in the same row
meets the “criteria”
§ Next
add this formula in the resulting cell to calculate the Sally Cat sum: =SUMIF(E:E,”1”,C:C)
* Excel expert note:
The “sum range” is optional. If left out, the “range” becomes the “sum range”.
But I usually specify the “sum range”, as my “range” usually is not the same
column as my “sum range”. Leaving the “range” as the “sum range” is useful if
you do a sumif that includes a formula criteria on numbers that you also want
to sum. For example you could SumIf a list of numbers on only those numbers
that are greater than 5.
b. Analysis: SUMIF can be much more
efficient than the previous two methods. But it can be even more helpful in
that if you add the formula before adding the flags, you can watch the formula
balance increase or decrease as you add or remove flags from the different rows.
Try it out! The downside is there’s
still a manual element to this, which involves going in and adding the ‘1’s and
‘2’s next to every row you want to include.
c. Additional note: The second part of the
formula, the “criteria”, can reference a cell that contains the specified
criteria instead of hardcoding the criteria. For example, see below to easily
add up the Sally Cat and Sally Dog amounts using this formula in I3: =SUMIF(E:E,H2,C:C) and
this formula in I4: =SUMIF(E:E,H3,C:C)
Here’s with the formulas showing:
As
you can see, SUMIF with reference to a cell for criteria is very useful because
you can add additional flags all day long, and just copy and paste your SUMIF
formula.
SUMIFS formula:
a.
How: The
SUMIFS formula will only work if you have reliable and consistent data, in the
exact same format throughout the data. If you do, this is a fantastic way to
add numbers using multiple criteria. The two criteria we are using here are
Name (Sally) and Type (either Dog or Cat).
·
This one gets a little more complex, but I promise
it is easier once you understand how it works.
· The
SUMIFS formula, like the SUMIF formula, involves RANGE, CRITERIA, and SUM
RANGE. However with the SUMIFS, you can have MORE THAN ONE criteria. It’s
organized as follows: (sum range, criteria
range1, criteria1, [criteria range 2, criteria 2], etc……)
·
Given our Sally Cat calculation, we’d use the
following formula: =SUMIFS(C:C, D:D,"Cat",A:A, "Sally")
·
And yes, you guessed it, to add up the Sally
Dogs just replace the word “Cat” with the word “Dog”
Here’s with the formulas
·
And again, you can use formulas to reference the
cells containing “Cat”, “Dog”, and “Sally”:
·
(Note the Dollar
sign ‘$’ in the formula: This is used to keep the ‘H’ and the ‘2’ the same even
when the formula is copied and pasted to different rows or columns)
·
(Fun tip: CTRL+~ is
a shortcut to show all formulas in excel. Press it again to hide formulas.)
b.
Analysis:
If data is in a consistent format, this is great way to do multi-conditional
sumifs. I used to use these quite a lot, however I graduated to pivot tables a
few years ago...
Pivot Tables
a.
How: There
are several things you must do before creating a pivot table:
·
First, make sure your data is correctly
formatted. No blank columns, no blank rows, and all columns must have a label,
preferably unique. The example above is almost ready to go, except it has no
header for the Names, therefore we must add a header (I used “Name” in cell A1)
·
Next select the data you want to include in the
pivot table. In our example, it’s A1:D29.
You can select it by pressing CTRL+A anywhere within the data, just look
to make sure it correctly highlighted what you want. Here’s what it should look
like:
·
Now go to INSERT > Pivot Table
·
Make sure the correct Table/Range is selected,
and the New Worksheet is selected, then hit OK.
·
Now we have a Pivot Table, but note nothing
shows up until we tell excel what data goes where:
·
Definitions:
1. Here’s
where the pivot table results will go
2. These
are the columns, or Fields, that we pulled into the pivot table data. To create
the pivot table, we must pull several of these Fields into one of the four areas
in the bottom right quadrant. Typically you at least will want to have ROWS and
VALUES.
3. Filters
are used to filter the results.
4. Columns
take the data in whatever PivotTable Filed you want to display as columns.
Easier to understand this one when you
see it in action
5. Rows
are my favorite. These are used to setup what we will summarize results on
later, and we will use the RANGE from the SUMIF formula for the rows
6. Values
take the Field input, and summarize or count based on the ROWS and COLUMNS that
match. Again this makes more sense when you see it, but I’ll show how we can
create a pivot table that gives us exactly what we created using the sumifs
formula.
·
Setting up the pivot table to mimic the SUMIFS
(to move, click and drag the fields from the top area to the bottom quadrant):
1. Put
Name in ROWS
2. Type
goes in COLUMNS
3. Number
goes in VALUES (make sure it is “SUM” rather than “COUNT”)
4. Date
is not important to us at this point so we will not use it.
·
Note how easily we can now see the total cats
and dogs for each person!
·
Now if we want to just see Sally’s you can
filter using the dropdown arrow next to “Row Labels”, uncheck “select all”, and
check Sally.
Click “OK” and
BOOM!
b.
Analysis:
In my opinion, pivot tables are clearly the best here, as they provide a
very versatile and easy to use format. The data can be literally “pivoted” in
different directions, filtered in different ways, with ease.
Cheers!
Daniel
Tuesday, May 19, 2015
Adobe Split Window!
Happy Tuesday!
I re-discovered something really useful about Adobe
yesterday. If you are looking at one PDF file and are finding yourself flipping
back and forth between different parts of the file over and over (e.g. if you
are comparing contents from one page to the contents of another page), there is
a very useful feature that allows you to see two parts of the same document at
the same time.
Just click and drag down this little bar on the top right of
your document, and it will split the screen!
In this example. I’m looking at the US Government’s 2014
Financial Statements, and want to read the notes to the financial statements
that relate to the specific liabilities line item of Federal debt securities
held by the public and accrued interest (Note 12).
Once you’ve split the screens, you can independently zoom in
and out, and scroll up and down in each of the two parts of the window.
And now here’s how it looks dragged down. Note I have zoomed
in on the top part, and zoomed down and scrolled to the Notes in the bottom
part.
If you want to scroll up or down in either section, simply
hover your mouse over that part of the window and use the mouse scroll wheel.
No need to click on it first.
Enjoy!
Friday, October 31, 2014
Beware of CSV files!
If you work with excel files a lot, chances are you’ve had
this experience. You start working on an excel file, working hard on
formatting, adding and editing formulas, highlighting, creating pivot tables
and extra tabs, and as soon as you save or email the file, you realize all your
hard work was lost! Lo and behold, what you thought was an excel file may have
been a CSV (Comma
Separated Values) file.
CSV files are very useful, in that they can take a large amount of data and store it in a much smaller amount of space than if you were saving excel. You’ll see lots of different reporting and data software that exports reports and files into CSV format. The sneaky thing is when you open these in excel, they look just like an excel file, and excel even lets you format, add formulas, and add tabs – but when you go and attach the file to an email or save it, all of those formatting changes, formulas, and extra tabs go away! So make sure to save the file as an xls or xlsx file before saving or emailing.
Cheers!
Friday, June 20, 2014
Tech Tip - Powerpoint Slide Numbers Aren't Showing!
Have you ever tried adding a slide number into PowerPoint slides, but you can’t get the slide numbers to show up? That happened to me today, and I found a useful blog post here which I’ve conveniently re-posted in this post. The trick works! But the trick for me was I had to select all of the master slides and check the “footnote” box.
When the Slide Number Doesn’t Show in PowerPoint
If you’re working in PowerPoint, and you want the slide number to display on the slide, the process is generally simple:- Go to the Insert tab.
- Click the Slide Number button.
- On the Slide tab, check the Slide Number box.
- Click Apply to All to make the slide number show on all slides, or Apply to make the slide number show on just the current slide.
Check to make sure your slide master has a slide number placeholder.
The slide master is the template for the entire presentation. If the slide number placeholder does not appear on your slide master, then it will not appear on your slide, even if you try to insert it through the process above.1. Check to see if the placeholder appears on your slide master.
- Go to the View tab.
- Click on the Slide Master button.
- Click on the outdented slide in the panel on the left. It should have a 1. next to it.
- Look at the slide in the enlarged view on the right and see if it has a box along the bottom (or anywhere, really), that has a # sign in it. This is the slide number placeholder (circled below).
2. Add the slide number placeholder, if you do not see it.
- Go to the Slide Master tab.
- Click on the Master Layout button.
- Check the box next to Slide Number.
- Click OK.
3. Check the indented slide masters for each individual slide layout.
In the left pane of the Slide Master view, you’ll see a different slide representing each possible layout. See the screenshot to the right for examples. You’ll need to check each individual layout slide master to see that the slide number placeholder is visible in that layout (assuming you want to see a slide number when you use that layout).
- Click on one of the individual slide layout masters. If the slide number placeholder appears, check the next individual slide layout master. If not, continue reading.
- Go to the Slide Master tab on the ribbon.
- Check the Footers box in the Master Layout section of the ribbon. The slide number placeholder should appear.
- Repeat for each layout on which a slide number should show.
When you are finished working with the slide master, click the Close Master View button on the Slide Master tab of the ribbon so you can continue to edit your presentation. Then follow the instructions at the top of this post to insert slide numbers on your slides.
Is your slide number covered by a shape or other object?
If you’ve inserted slide numbers, you’ve checked that your slide master has a slide number placeholder, and you still cannot see slide numbers on your slides, check to make sure there is not an object on your slide covering up the slide number.To see all objects on the slide click once on your slide and the press Ctrl + A on the keyboard. This will select all objects on the slide, even the ones you might not see because they are the same color as the background. See if there is something in the bottom right corner of the slide, where the slide number usually appears, that might be covering it. If so, deselect the objects by clicking once in the grey area outside your slide, then select the covering object by clicking where you saw it on the slide. Either delete it, by pressing the Backspace key on the keyboard, or move it by clicking and dragging the object.
Didn’t find anything? Check again on the slide master (View tab > Slide Master button). Be sure to click Close Master View when you’re finished working in the slide master.
Do you have more than one slide master?
It is possible that your slides use multiple slide masters. This happens when additional slide masters are created. It can also happen if you paste slides that use a different slide master from another presentation into your presentation.To see if your presentation is using multiple slide masters, go back to the slide master (View tab > Slide Master button). In the left pane, scroll down and see if you have more than one set of slide masters (each set will be numbered). Either determine which slide master your presentation uses and add the slide number placeholder to that master and each layout, or add the slide number placeholder to every slide master and every layout. You can determine which presentation slides use which slide masters by hovering over the slide master. A pop-up note appears telling you the name of the slide master and which presentation slides use it. The screenshot below indicates that the Office Theme Slide Master is used by slide 1 of my presentation. Be sure to click Close Master View when you’re finished working in the slide master.
Enjoy!
Subscribe to:
Posts (Atom)