Loading...

Follow Excel TV Blog on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


We want to make our blog better and more relevant to your needs and interests. To do that, I need to know more about YOU. As a result, we have created the 2017 Reader Survey.

Click to Launch the Survey

The post Please Take Our 2017 Reader/Viewer Survey appeared first on Excel TV.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this episode, Oz discusses the dos and don’ts of a sticky situation: when one’s data analysis is wrong! If your work involves a lot of number crunching, do read it.

Let’s get started. 1 – The Situation

Oz was processing commissions for some month and the numbers just looked plain wrong. He checked the previous month’s file and found out a blunder in his analysis: a person got paid $5,000 than they should’ve gotten last month. Although he feared looking stupid and getting fired, he still informed his superiors about the error.

2 – What Not to Do

Not taking ownership of one’s mistake and trying to hide it is a serious problem. Once caught, it can lead to dire consequences: from losing one’s job to landing in jail.

3 – What to Do

In each organization, there are processes and review mechanisms in place to catch such errors. But even than some errors might just slip away. The important thing is that the error was eventually caught and Oz took complete ownership of it. Also, the error highlighted a weakness in the processes governing disbursement of commissions.

4 – What’s the Take-away

Firstly, the blame does not rest with the tools an analyst uses. The analyst needs to own up to it.

Secondly, playing with data is like playing with fire; sometimes one will get burnt. It is important to accept and learn from one’s mistakes.

What’s next?

Share your experiences and advice with us in the comments section below. And do not forget to forward this video to your friends and colleagues.

Also… Check Out Oz on Youtube

The post SSSVEDA DAY 5 – When Data Analysis is Wrong appeared first on Excel TV.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Rick:  Next up is our special guest Jon Acampora. You may remember Zack Barresse mentioning Excel MVP John Acampora of ExcelCampus.com. Jon, say hello and tell us what ExcelCampus is about?

Jon:  Hello and thank you for having me. ExcelCampus is a blog that is about learning how to use Excel and saving time with your everyday tasks. I have a bunch of blog posts with in depth articles on there.  I do videos and video tutorials. I have developed quite a few add-ins that you can download. The majority are free. It’s growing every day.  It is a fun place to hang out and learn about Excel.

Oz:  How did you start?  Did you grow up saying I want to build Excel add-ins?

Jon:  Who says that? I want to meet that person. It started in my first job after college. They were using Excel for everything; inventory, planning, accounting.  I think most small businesses use Excel for a lot of things. I was immersed right away and fell in love. It’s hard to get away from it if you are in the finance or accounting world. I think it’s cool. It’s been a journey ever since. I started developing add-ins. Every company I’ve worked for has had problems with processes. There is a lot of room for automation. So that is how I got into add-ins; seeing the need to do things quicker and efficiently.  I hate doing repetitive tasks. I get bored pretty quick. With Excel, you can program it. It is an amazing tool. There are endless opportunities.

Oz: Do you worry about becoming a dinosaur one day. Do you wish you would have learned JavaScript or something else?

Jon:  Yeah, part of me does. I’m very open to learning other languages. But there are companies still using Excel 2003. I don’t see it going anywhere. So it doesn’t scare me. I’m fine with that.

Jordan:  You should not be fine with that!

Oz:  What do you do day to day? Do you do consulting? Do you meet with small businesses?

Jon:  I still have a day job. I’m a finance manager for a software company in Southern California. I just started doing the blog on the side four years ago. I really didn’t start doing much until the last year when I started writing more articles, posts and different VBA utilities. I’m still doing the day job.

Rick:  You talked a little bit about Tab Hound and some of the software you are putting out there. Will you talk about how you went about developing an add-in? What is the process like?

Jon:  It is not easy. I think my first add-in was a tool bar I developed internally for a company I was working for that was using an Oracle product for forecasting, planning and that sort of thing and then a spreadsheet interface called S Base. I developed a tool bar in the ribbon that automated a lot of tasks because Oracle sunsetted that product. I have done smaller scale automation before using the ribbon. I used Ken Puls book to learn about the ribbon. From there it was, “Wow, there is so much potential to do other things to streamline processes and automate things that you might spend time doing.”  I’m still working on a lot of different add-ins.  I have a lot of ideas.

Oz:  I’m curious about when you go to develop an add-in.  Where does that happen? People don’t know about going to developer tab and pulling up tabs. At what point are you in add-in territory versus formulas or VBA?  Where does the add-in happen?

Jon:  Alt+ F11 is the short answer. You can start writing macros there and you can write modules in your workbook. Everyone learns usually by recording macros using the button on the left-hand side. You can record macros and then you will be able to figure out what you did.  The code is easy to read.  It is called Basic for a reason. It’s very powerful at the same time. You can save a lot of time if you’re doing repetitive task all day.

Oz:  An add-in is written in VBA code?

Jon:  Yes. Basically, an add-in is installed in Excel and it opens up every time.  So you have that code there every time you open up Excel. A macro is just limited to a workbook whereas an add-in takes it a step further and is in Excel no matter what workbook you are in it operates.

Rick:  On the live show blog, Ryan Wilson is talking about how he used it. I want to encourage everyone to answer questions on John Acampora’s blog.  Jon, give us an idea of an Excel add-in. What are you talking about when you talk about an Excel add-in?

Jon: On my downloads page of my website, I have a list of add-ins I have developed, such as Tab Hound or Quarter Sum Formulas; tasks we do quite often. This add-in sits in the toolbar.  Just click on it and it will automatically create the formulas you need.  Microsoft itself is developing a lot of add-ins like Power Pivot and Power Query to make life easier with sets of data.

Rick:  Can you let everyone see your page?

Jon:  This is just some of how my add-in works.  It downloads in a zip file.  I have articles on how to install it to step you through installing an add-in in various Excel versions. The add-in tab will be there every time you open Excel. On this page, there are details on how it works.

Jordan:  Two things. First, we can tell how cool you are because I see your bookmarks are surf and snow. So, you’re a cool guy. Bookmarks are like your radio presets.

Jon:  I live in Southern California and my wife and I surf quite a bit.  It’s a lifestyle we strive for.

Jordan:  Second, can you tell us about the name ExcelCampus?  How did you come up with that?

Jon:  That’s a great question. When I first started doing this, I read a book on blogs and content marketing. I bought a few different domain names with the word ‘Excel’ in it. I didn’t like any of them like SpeedyExcel.  I wanted to talk about doing Excel things faster. Then one day, I was driving home and I drove by a road named Campus by a college campus. That was it. It kind of stuck. It was all a road sign.

Rick: That has to be another whole episode; available Excel URLs.

Jon: Yes. That’s true.  I bought quite a few.

Rick: Even the bad ones are taken. You think you have a good one, but there is no way it is available because it’s bad. Excel on a stick, or something, gone!  Not only .com, excelonastick.ca,.net, .info.

Jordan: .Biz is gone.

Jon:  You got Excel TV show!

Rick: Yes. Thank you.

The post Jon Acampora of ExcelCampus.com  appeared first on Excel TV.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Excel TV Blog by Lesley Davidson - 5M ago

Nick Green of EchoXL.com joins Excel TV host Rick Grantham and cohost Microsoft Excel MVP Jordan Goldmeier and author and Excel TV Host Oz du Soleil on Excel TV discussing the development and release of his Excel forecasting tool at EchoXL.com.

Rick: Our special guest is Nick Green from Columbia by way of Australia. Welcome to the show, Nick.  Will you start off by telling everybody about your Excel background and your background in general?

Nick: I was introduced to Excel in supply chain industry after university. I didn’t learn much Excel studying business in University, which is funny because I use it in 50% of my work. Supply chain is quite a chaotic industry and chaos tends to mean people use Excel a lot. So I was forced to self learn. I wish I had studied VBA more formally because it would have saved me a lot of headaches. If you don’t have the structure for it now you pay for it later.

Rick: Can you explain about the EchoXL name?  What is your site and where did the name come from?

Nick: EchoXL is a forecasting tool. When I think of forecasting, I think of echoes because the further away you look the softer your signal is much like an echo. And, echo is short. EchoXL.com is a forecasting tool. I’ve used a lot of forecasting software as a demand planner on my job and I thought those tools were built by statisticians for statisticians. There were so many people who had to do forecasting that were intimidated by these tools so I made something with simplicity to get them 98% of the way while automating and simplifying the process. That is what EchoXL tries to do.

Oz: How do you get from chaotic to meaning people use Excel a lot?

Nick: Because a lot of these companies have multimillion-dollar, streamlined software but Excel is popular among users because people underestimate the need for flexibility. All these businesses have quirks in their softwares that need to be adapted. Excel becomes more relevant, especially in supply chain.

Oz: What is an example of how things change fast in supply chain?

Nick: For example I worked for a cosmetics retail company where I had to forecast sales and make sure their inventory and stock was in the right location across the world. Each location had quirks. Inventory would have to meet special events or did not meet [historical] sales. Their software was rigid and wouldn’t cater to that, so we had to build similar software with Excel. There isn’t much out there with Excel forecasting tools in general. I’m trying to fill that void.

Jordan: When you built your tool, you had a version with no VBA. What advantage is there to taking VBA out and why did you want to?

Nick: The first version I sent to you was a beta version with VBA. I started with VBA and tried to make it work like an application. The ribbons were automatic, but I found when I sent it to people they would have problems setting it up. It wouldn’t get through the firewall or you couldn’t use it. If you’re sending it to friends, that’s OK. But if you’re sending that to customers, they get impatient and give up. I wanted to make something completely stable. If you work with constraints and a little VBA, what is embedded in Excel won’t work. Echo light has no VBA. I will be making more advanced versions with VBA that will be more heavy duty for organizations.

Jordan: I still love the VBA, but over time, as I learn more formula tricks, I came to feel like it’s overused. In a certain sense, less is more. You can do a lot of sweet things and efficient things with formulas and you didn’t have to use a run button. [Jordan hates run buttons!]. Formulas may be difficult, and VBA is easy, but I hope more developers will do more with Excel formulas because you can’t break them and it makes a lighter and tighter Excel application.

Oz: I would add, from bloggers that pointed out in the past, that if you have some big complicated formula, you are more likely to find somebody who can help you figure it out. But if you do VBA, you’re going to have a tougher time with somebody figuring it out. And when you get into writing code, a person may be more likely to say, “I don’t want to figure it out, let’s start from scratch.” So formulas are good for those reasons. I try to stick with formulas.

Rick: I see some irony in this, Jordan. This will probably be Bill Jelen’s favorite day. I’m a guy who owns Option Explicit VBA Blog (and author of Dashboards for Excel) thinks VBA is difficult!

Jordan: I know I have evolved. I can’t argue.

Oz: We have to have room to evolve or we have politicians that are 60 years old answering for things they did when they were 19. [Insert Chappaquiddick or other relevant pun here]

Rick: Nick, you use quite a few forecasting methods, can you discuss those and how you integrated those into Excel?

Nick:  There are four forecasting formulas, but the most important one is called Winters Forecasting. Jordan is familiar. It is triple exponential smoothing. You need to run formulas in the background and hide them from the end-user because it’s complex. You are basically separating your trending and seasonality from your forecasting. You have variables that will determine how far back the forecast will look, for example several months or several years. I did everything in a processing sheet.  You can go to VBA if you’re not enabling macros and you can go to Properties Section to hide the sheet. I think they are less breakable if you hide the sheet. It took a lot of trial and error and headaches, but it integrated them.

Rick: We talked about part of the development process. I did things in VBA and then I realized that was a problem. John Peltier talked about when you build for yourself, and then you build for your friends, there is growth in that.  Then when you send it out to other people, you go to a further level of development. Can you talk about what led up to that? What did that development process look to you, for the benefit of our viewers?

Nick: Like most of your viewers, I built applications for myself and close colleagues. But when you have to build it at scale for people you don’t know, you have to hold their hands through using the template. The difference is you have to think about that user interface design or user experience. You have to imagine what the lazy impatient user is going to think almost like you’re writing an article. People using things for the first time want it to do what it’s supposed to do and not be annoying. Simplify. I added many features and I asked myself if the benefit of that feature was worth the extra weight and the extra complexity it added to the interface. I had to trash so many ideas and features I had spent days building.

Oz: Can you give us an example of what is a good idea that might not be worth having?

Nick: Initially, I had more tabs that would allow the user to control everything such as custom seasonality or weight given to Christmas sales, but the benefit was so small versus the learning curve and confusion introduced. So even though I put my sweat into it, I had to be honest and scrapped it.

Jordan: for our viewers, Nick, when we’re talking forecasting, for less statistically minded, we’re not just talking a weather forecast. Can you give information for forecasting in your world?

Nick: There are two kinds of forecasts in business and demand forecasting. There are finance people and engineers who have to forecast and predict sales of a product, say. The main one I’m referring to is historical forecasting where you’re looking backwards at historical trends and seasonal flows for a typical consumer product, for example, products with sales popular at Christmas, but diving in January. You use statistics to rationally predict and extrapolate the data into the future, basically relying on guest work.

Jordan: Using historical data to make guesses about the future.  The different methods you have in your tool account for trends or cycles and looking at the past to get a pretty good guess about the future.

Nick: It is guessing. The least worst guess.

The post Nick Green of EchoXL.com appeared first on Excel TV.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Hello Excel.TV fans!

As you know, Rick and I have been working super hard on developing awesome new training courses for you. At the same time, I’ve also been working on something secret—and I’d love to let you in on it now that I’m allowed. Excel.TV and Logitech have been working together on something exciting!

So here’s what’s up. Logitech reached out to me earlier this year to tell me about an exciting new product to make using Excel even easier and to make your time looking at spreadsheets more productive. At first, I have to admit, I was super skeptical. I get pitched a lot of products (usually of the software variety) and most of them are cloud-based spreadsheets and Excel add-ins that have been done before.

So I was particularly interested in Logitech’s pitch, since this wasn’t software but hardware—a new keyboard in fact. A keyboard called Craft.

Logitech Craft in Microsoft Excel - YouTube
The Craft Keyboard

I’ve been using this keyboard for about six weeks and I have to say I’m pretty impressed. It’s well constructed and really enjoyable to use. I say this as someone who usually buys the junkiest keyboard out there thinking there’s little difference between them. But I have to say, the Craft keyboard made a compelling impact on my work. However, as Logitech will tell you, this is no ordinary keyboard; the real cherry-on-top is the noble, touch-sensitive wheel regally title the Crown.

Below, I begin the first part of my review of the keyboard, I talk about the Crown, and I let you know how you could get your hands on one for FREE!

Construction

I was immediately impressed (and surprised!) by how well this keyboard was designed. Unlike your typical plastic keyboards, this thing has weight—it doesn’t feel like a toy as computer peripherals often do. Despite the thinness of overall keyboard—and the shallowness of the keys—typing is a satisfying experience with the right amount of pushback. But perhaps the best part for me are those rubber grips. Rubber grips are the first thing to go on keyboard (either they wear out or their glue dissolves) but these ones aren’t going anywhere.

One thing that took getting used to was how flat the keyboard lays against the desk. You can see it sits with only a slight incline.

As someone who started typing before the age of laptops, I still sometimes prefer a greater incline while I type, so I rotated between propping the keyboard up a bit to give me some depth and having it lay flat. After a while, I started to prefer having it lay flat.

The Crown

The Crown is perhaps the most interesting and daring aspect of this keyboard. Logitech built functionality into the Crown with the everyday Excel user in mind. You can see the Crown being used in the animation below.

The Crown works like a customized context menu. Depending upon where your selector is in Excel, specific menus will pop up. Here’s a screen recording I took of the zoom and scroll features.

Getting to the menu is super easy and requires a simple tap of the crown. Here’s a still shot of the context menu that shows up when you select a column.

As of the latest software release, there was already a lot of functionality built into the crown. And I’ve been working with Logitech on adding even more features I think Excel users will love.

One thing I loved was how much you could get done with just the keyboard and Crown together. As most Excel users know, the keyboard is our main tool—especially if we take advantage of all those keyboard shortcuts! Using the Crown was like getting access to the shortcuts I love without pressing (and holding) so many buttons!

Experience

Logitech was kind enough to throw in a super nice mouse to use along with the keyboard. And this is perhaps where I found myself the most surprised. Like I said, I have a terrible habit of being skeptical of everything (perhaps it’s the data analyst in me). But I genuinely felt using the keyboard (and mouse!) helped my productivity. Like I said above, it’s neat that you can do so much and without having to look at your hands as you do it.

When I initially spoke with Logitech they described the Craft keyboard and Logitech mouse combination as allowing one to get into their flow more quickly. Flow is that magical place where you forget you’re actually developing in Excel or even working on a computer. You’re just in your zone. We all know that place. And, if you’re like me, you like to find the perfect conditions to create it.

I found myself quickly getting lost in my work (in a good way!) using the Craft keyboard along with the Logitech companion (albeit not Excel specific) mouse. There’s just something about the way these two devices work together. Even when I was surfing the internet—and not wrangling data—everything seemed much more effortless. But even the keyboard on its own was impressive.

And the real winner is using it in Excel. It took a while to get used to at first but over time I started integrating the Crown without realizing it. I think my favorite feature is being able to automatically insert an Excel table. Excel Tables are among the most valuable and underused features in Excel. And I just love how easily you can get to them. If nothing else, I hope it encourages more users to insert Excel Tables.

Summary… and next steps…

Logitech has just released this keyboard and it sells for $199. This is much more than keyboards usually go for but then this is no ordinary keyboard.

I’m assuming you’ll want to get your hands on and so you’re in luck. As part of our partnership with Logitech, we’ve secured several keyboards to give away! We haven’t yet hammered out the details of the giveaway (whether we make it a lottery or contest) but if you want the chance to get your hands on one, sign up below.

Over the next few weeks I’ll be providing more in depth reviews of the Craft keyboard—what I really liked and what could be improved. So stay tuned! I’m excited about the potential because the keyboard is the most important Excel developer’s tool—and this is the first improvement I’ve seen in years that I’ve liked.

Logitech imagined building this for you, the everyday Excel user. So in the comments describe what you’d like in an enhanced keyboard. What Excel features do you want with only a tap? 

Photos courtesy of my wife, Katherine Schmidt. 

The post Introducing the CRAFT keyboard, a new way of working in Excel! appeared first on Excel TV.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Excel TV Blog by Lesley Davidson - 6M ago

Rick: Welcome to our special guest straight out of Canada, Mr. Ken Puls of excelGuru.ca. I first became aware of Ken a year and a half ago when I was stalking his Twitter feed and Facebook page. He was about to get 100 Facebook page likes and he said whoever is my hundredth like I am going to give away a pivot course.

Ken: So Rick unliked my page and waited eight hours so he could be the hundredth person to like the page.

Rick: Ken finally said I wasn’t the hundredth and even if I was, it was kind of cheesy because I was stalking him for eight hours.  Say hello to everyone, Ken.

Ken: Hello, my name is Ken Puls.  I live and work up in Canada in God’s country on Vancouver Island. My day job is controller and director of IT at a resort. And in the evenings and weekends, I put on my superhero cape and do Excel work. I spend my time writing website articles and teaching how to use Excel better. Fun stuff.

Jordan: Tell us about ExcelGuru?

Ken: What specifically? It all started as a hobby, believe it or not. Back in 2000, when I started working at the resort, I was trying to figure out how to get some things done and I stumbled onto VBA. I started teaching myself and I ran into a problem and couldn’t figure some things out. Our head office came in and cut our staff by a third. We needed to get more work done with less staff.  I was the guy in the office who knew most and the only way was to get more done was to learn VBA. So I spent a lot of time in forums. I felt guilty about all the knowledge I was stealing from these groups for free so I decided I needed to give back to the community and reinvest. I built a website to show some of my samples. That’s where it started.

Rick: What is the top 30 under 30 award?

Ken: It was the 20 under 40. I’m not under 40 anymore either. I submitted an inch of stuff I’ve done so clearly it was awarded by weight!  Not to cheapen it, they were looking at what people have done and how they’ve helped people in the local community in different areas. I like to think that I’m the only person on the list who can say they helped someone with their business sending things out of our atmosphere. I have an example where NASA used some of my code to monitor power levels on the space station. Pretty cool.

Rick: I noticed that it’s hard for me to go to a training site without hearing your voice.

Ken: Good. Excellent.

Rick: Weird. I look at the credits and there is Ken Puls. What training do you deliver on the web?

Ken: Not nearly enough. I have done a curriculum with certified general accountants up here through their website where we go into a professional studio with hair, lights and make up. I built a curriculum for a company in Australia called Go Skills. There are about 36 videos of 5 to 10 minutes each. I have my own Pivot Table video and some YouTube video tips. I’ve lost count. I’m showing up everywhere.

Rick: Particularly the Go Skills stuff. This guy is everywhere.

Ken: I enjoy doing that.

Oz: What kind of things go into building a curriculum?

Ken: Blood, sweat, and tears.

Oz: What kind of questions would you ask and how do you set yourself up so you can deliver?

Ken: Basically, I approach it either feature first like how do I teach them how to use features or building a curriculum that teaches going Point A to Point B on a specific topic. For instance, in order to use pivot tables, we have to teach people how to clean up their data, then build tables, and then we’ll build pivot tables. If we take a broad brush course for Excel, we are going to focus on general stuff. Different things are going to different courses. Personally, I like to have good examples when I teach. People really resonate with examples. Techniques are good, but I need to see how to put it into practice. For me, I like to use the example of liquor store stats; beer, wine or cider. When you start using alcohol, people set up and listen…as Rick drinks his beer.

Jordan: I had to develop a curriculum and there is always this tension between what I want to put in and who’s producing it. I want to put in too much. Is there ever tension with what you want to cover? What do you do about that?

Ken: I have been lucky about that. The groups that approached me have been tolerant. They have asked what it is going to take and how much time will it need. One of the curriculum we build is 10 courses for CGA with more in the pipeline. I’m still hopeful it will all be completed. There are forty to fifty 1-2 hour courses in the pipeline. Go Skills had a different target. The focus was five minutes videos of different topics.  When I wrote the Ribbon X book (RibbonX: Customizing the Office 2007 Ribbon by Robert Martin, Ken Puls and Teresa Hennig) with Robert Martin he named me “verbose man.”  You have to scope back.  There is can-do, absolutely have to do, and stuff that falls on the cutting room floor.

Oz: It is disappointing to me and students when I’ve packed so much there’s very little room for questions. I’m OK with leaving some stuff out.

Ken: It depends on the medium you’re using. When you are recording videos for someone, you don’t have the opportunity to do questions.  It is totally different. I love live training. When I teach, it turns into an eight hour course. Things slow down and people are asking questions. When you start getting those questions back, it brings up other topics. It adds flavor to the live training.

Oz: Immediate feedback is good.

Ken: The tough part is when you record video, you set up a script.  But when you teach live, you know when they get lost. Last week, I did a webinar and I’m speaking to them live through screen sharing and I can’t see them so it’s tough because you don’t know what is going on. The most difficult to teach is in a webcast. I still enjoy it. But if I’m learning, I want to be in a classroom with instructor or with a book instead of webcast.

Oz: I like sitting in a classroom or with a paper book. I don’t like putting the question in that chat box and getting a half-assed answer.

Rick: When you started ExcelGuru, you said this was an effort to give back. Can you talk about the evolution of ExcelGuru?  It was a big jump to create a forum. How did that developed for you?

Ken:  Originally it was four pages when it started. I bought my own domain and had a front page. I tried to teach myself how to write HTML, but that’s not where I want to be. The deal was how do I get content out there?  I put up four examples like using VBA to move data to access etc. and as time went on, I started to add things. But HTML was too hard so I found an open source content management system. Then I could focus on writing my content instead of coding my content.  I added a WordPress blog. I wanted to have a place where I could throw some random content or rants. I built the blog and attached it to the site. This was all done on a very cheap Linux hosted program.  I used open source software and spent about two dollars a month. I eventually decided to start my own forum when I had 60 to 70 items.  Someone at Excel MVP said just do it. So I added the forum. Baby steps!  I do worse at selling stuff on my website, which I should be doing. I seem to spend a lot of time working for other people than working for myself. It has evolved over time. We’ve been around for 10 to 12 years. I see people that started with a free blog from WordPress and they’re doing great. This huge mega thing called the Internet makes it easy now.

Rick: What were your biggest steps going to the forum?

Ken: I’ve been lucky. The website was an interesting step because I had to learn HTML. When I added the blog, that was another learning journey. The whole time I’m figuring this out, I am learning about Excel. The forum was a huge step because that was the first piece of software that I had to pay a licensing fee for. I installed it and it took a ton of time to get it set up, configured and working the way I wanted it. I’ll be out front and say that if it weren’t for the people there posting and volunteering their time to answer questions, it would be a shadow of what it is today. I’m very thankful for everyone who contributes. I wish I had more time to contribute myself. There’s a good core group of people that do a ton of work on the forum. Huge shout out to the moderators. When we got to the forum, software was the linchpin.  I realized that learning and programming web language was not my forte.  I love business intelligence and VBA and Power Query and Excel formulas. I have had to learn many other languages, because I’ve been too cheap to pay other people to do it.

Oz: I’ve been frustrated with Excel and dashboards I could build.  I’m on the web, but you have to know JavaScript. Did you ever feel frustrated that Excel wasn’t web friendly? What do you think about the things they’ve done to get Excel friendly?

Ken: I think it’s good. Microsoft’s role going forward is shifting to web content from the desktop. What I tell people is the role of Excel business intelligence report developers is changing. We are becoming programmers. We are going to write our solutions on desktop in Excel. The dream is that it will just work; integration with SkyDrive, push to Power BI. I want to publish into a web platform so somebody can be on the beach in Mexico reading it on their mobile device and interact. The challenge is that my data is stored in my SQL server and not accessible. Now with Power BI coming out, they fix that data connector allowing you to build your data connector locally that pushes it up to a Power BI site. That is starting.  Right now, I send my reports out with automated PDF’s, so if it doesn’t happen I’ll continue that.  I don’t think the world is going to revolve around the web.

Oz:  Why do you say that?

Ken: Some people have a concern about internet connectivity.  For example, someone said they have concerns about refreshing because they have no Internet connectivity in Africa.  I have to be able to have my stand-alone solutions. So that is a concern for people.

The post Ken Puls of ExcelGuru.ca appeared first on Excel TV.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The theme of today’s episode is about finding better ways to get the job done. And nothing helps take the Excel community towards this end better than sharing of Excel knowledge. To demonstrate this, Oz will be describing one of his own experiences from the early days.

Let’s get to it. 1 – Doing the Wrong Things for the Greater Good

Oz started the episode with Alex Powers’ (of IT’S NOT ABOUT THE CELL) Facebook comment about a blog post. He mentioned that experts in the Excel world spend a lot of time doing the wrong things. This helps share knowledge with others to enable them to do the right things. Hence, this sharing of knowledge helps shorten the learning curve tremendously for many Excel users.

2 – His Own Experience

Oz mentioned one of his own experiences from the start of his career.

He had two reports: one containing thousands of transactions for the month and the other had name and id data of customer service representatives. His job was to match the two reports to calculate the bonuses to be paid. He sorted, filtered, copied, pasted and manually highlight cells, but eventually the job got done.

Turns out, the report Oz was handed was not the latest extract. And that he’d have to do all this all over again. 2-3 hours of effort completely wasted.

But he had discovered VLOOKUP in time for the second iteration. Now doing the work all over again hardly took a few minutes. The lesson to take away from all this: always ask yourself if there is a better way to get things done. Stop doing things the wrong way!

3 – Bonus

An entertaining section on vlogging (or video blogging) awaits you at the end of the video. Enjoy!

What’s next?

If something is taking a lot of time, try to find out if there is a better, faster way to do it. You have all the knowledge Excel experts have shared freely to leverage.

Write your thoughts to us in the comments section below. And do not forget to share this video with your friends and colleagues.

Youtube

And while you are at it… Check out all the great Excel videos on Oz’s Youtube channel.  He’s on Fire.

The post SSSVEDA DAY 4 – Sharing the Excel Knowledge appeared first on Excel TV.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Excel TV Blog by Jamani Arsalan - 8M ago

Today we have a live coverage all the way from our reporter Carl Pepperseed! Let’s get to it.

1 – Background

Andy McAdoo was able to combine five years’ worth of credit card statements, travel history and phone records, and merged them all in a spreadsheet. And with the support of advanced tools in Excel, he was able to compile evidence that his wife was having an extramarital affair.

2 – His Next Move

Mr. McAdoo did not stop there. He was never interested in using the FREQUENCY array function and make a histogram. He instead used Excel’s Power View to generate an animated scatter plot of the whole 5-year period.

Most men would’ve stopped there and confronted their wives, but not him! He went on to successfully predict the timing and location of the next rendezvous between Ms. McAdoo and her lover.

3 – The Last Straw

He set up a stakeout right outside the hotel. Unfortunately for him, he hid behind a fire ants nest. Now he’s in intensive care.

If only he had known he was supposed to figure out the spatial distribution of the fire ant mounds in the locality, he wouldn’t have gotten anaphylactic shock in his feet and loose teeth from falling on his face. But what’s done is done. Say your prayers, gentlemen!

What’s next?

If you enjoyed the news coverage, share it with your mates. And do write to us in the comments section below. Any thought, opinion or anecdote is welcomed.

The post PRAYERS FOR ANDY MCADOO appeared first on Excel TV.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview