What are the best practices when modelling in Excel?
Excellent advice. Wanted to add my perspective about Arnav's point about knowing your audience. Full blog post here What is a good excel model? Who is the customer? In my mind this is the very first question you have to ask because frankly 3 not all of your excel work will be seen by other people. Often it is just something basic that you put together. It may be just a glorified calculator. Perhaps it is just a pivot table with some VLOOKUP formulas. No need to format it and make fancy graphs because honestly you are the only one to use it. The moreplex the problem numerous the potential solutions or discerning the customer 3 the better your model needs to be. Using this graphic below you have to start at the bottom with the basics and build up to a real client-ready excel model.n #1. Is the model logical and accurate? At the very least the excel needs to make sense to you. Using the analogy of boat-building think of this like a raft. nTranslating that to excel it means asking yourself basic questions like What am I trying to calculate? What data am I using and is it clean? Is my data sample size (n = ?) big enough to be meaningful? How am I organizing my data so it does not look like a sock drawer? How is the data going to be analyzed? How do I stitch it together with math? n #2 Is it flexible? Once the data is organized and the calculations are working you can start the thinking part of the exercise 3 the actual modeling. Since the whole point of an excel model is to test the results make sure it is convenient to change things around. The last thing you want to do is make the excel model hard to work with. This is a tool to proto solutions and run what-if analyses. The excel should be like the dinghy simple efficient fast nimble. A few pointers might ben Don hard code ( in the number) for any calculations Reference the same source cells (so that 1 change affects everything) Use consistent formatting and structure when possible Don merge cells because it makes it hard to sum sort or filter Keep the data separate and clean (no subtotals no blank columns between data) n #3 Is it easy to use? The goal here is to make the model both easy-to-use and also easy-to-audit. After spending 4-5 hours putting this monster together spend a little time cleaning it up. You don want half-baked left-over calculations everywhere. This is critical because you want your team help to stress-test the model. Make sure it is accurate andprehensive. A good consultant knows how to use the team. Have other people to double-check your work. You need to have some thick skin and know how to take constructive criticism. Think of it like a pontoon boat that is easy-to-use and generally fun. Make it a smooth review process for everyonen Document the assumptions (data sources s etc) so it is easy to audit Take out unnecessary data sheets or things unrelated to the model. Just like an appendix of a book put all the boring data tables and reference stuff in the back If you have analyses (charts tables) put them in the front of the workbook n #4 Is it visual? If your excel made it this far it is actually a client-facing model. You have to assume that everything is transparent so nothing should be hidden. No hidden tabs no hidden columns. Make sure that the fonts match and the calculations are spot on.n Put an introduction or table of contents to ex the way the Excel is organized. If you want the reviewer to print out pages set up the print margins Save the file so it opens up at beginning (top left). It is disorienting to open the file up and it starts in the middle of a random worksheet cell AL453 Use macros to automate messy calculations but use this sparingly Create a dashboard on the first page that shows all the major metrics and status. Ideally this is the only thing they look at because it is soprehensive A client-facing excel model is like a yacht. It has all the bells and whistles and is very refined. A lot of the models made by investment bankers or valuation specialists are like this. Each font color has a different meaning (e.g. black = input blue = calculation) and the excels are formatted for easy navigation without a mouse. Note investment banking analysts pride themselves on the fact that they don use a mouse. All keyboard.
What is the best tool for converting technical programming PDF books to e-publications?
Calibre handles conversion very well. If the PDF book contains illustrations or extensive formatting you're SOL without extensive editing. This is one of those times when throwing hardware at the problem is more effective than conversion or software. Use a good PDF reader on the iPad or a large screen eink device with PDF support. If you are a publisher and have original layout files the job is a lot easier and you can contract with any number of experiencedpanies depending on the size of your back catalog and how quickly you want the files converted.
Why did Greek Prime Minister Alexis Tsipras use six different fonts and font sizes in his letter to Jeroen Dijsselbloem on June 30th, 2015?
Note italic Scroll down to Edit 1 to read what I think is the likeliest explanation. italic n horizontal-rule This is actually quite normal for a country struggling with debt. The last thing you want to spend money on is a salary to someone who might know a thing or two about font choices andbining faces. What youre then left with is someone who sees a buffet of available choices and like someone starved for days decides to gorge on it. Who needs to use a sober font when there this one where the uppercase characters rise like a periscope peeking over the next cubicle? And what kerning italic ? Is that even Greek? No it from the French carne italic and France sided with the Germans and Italians on that Eurozone vote so who needs that? Wasn it that David Carson guy who got famous for trying all sorts of treatments just so it would look good and screw anybody who actually wanted to read italic the articles? Let go for that look then and make it a right proper word salad. Why let make just parts italic of some words a different size. Let's see you beat that Carson. In all seriousness this letter has probably been crafted (and I use that term loosely) by someone who hasn the faintest clue what they were doing. This isn't about highlighting certain key phrases because the choice of words that have been used at larger sizes doesn't even make sense. The mix of styles and inconsistent sizes point to this being put together by someone unschooled in the basics of letter-writing let alone a secretary that has earned their position in that office. PS I actually reached out to a Greek designer friend to see if he could shed some light on whether it had anything to do with the Greek alphabet. According to him not a thing for the simple reason that the letter was in English not Greek. italic n horizontal-rule Edit 1 This may italic go some way in answering why the fonts are all messed up. So I downloaded the PDF of the letter posted on Politico here EU rejects Greek request for new loans . Politico claims to have obtained the letter so if it is the original PDF (and not something that Politico created) then the letter has two pages the one posted in the question description) and this page below See the part at the top marked in red? That shadow line is indicative of the second page being a scan rather than a PDF generated directly from an application like Word or Excel. Furthermore the PDF properties show this n One The date is shown as 3 June the same day as the letter itself so it's possible that this is the original PDF and not something created by Politico. Two The 'Author' is shown to be which is a free online PDF converter where anyone can upload MS Word documents JPGs Excel files etc and have them converted into a single merged PDF. If that is indeed the case then someone at the Greek PM's office uploaded two pagesa document using non-standard fonts and a scanned pageto the iLovePDF site with the aim of having them merged into a single PDF. Chances are that font ipatibilities between the uploaded first page and the website's available tools caused everything to go haywire. The second page being a scan contained no fonts and was not affected.
What is the laziest thing you have ever automated?
I was working at a satellite antenna manufacturer where they had several NASA and JPL contracts. They had a major shipment that needed to be made one week and the one shipping clerk they had who knew how to properlyplete the byzantine NASA shipping docs. process was out on maternity leave. So they gave the task to the guy who can never say no of course. Never knew anything about that process but I did know about a whole lot of other NASA paperwork so why not? What I didn realize was the way things were traditionally done it was a very dynamic process just getting the shipping docs.pleted. First there was a set of arcane forms like ten pages with blocks that had to all contain something or it was notplete. Most of the blocks represented the acceptance of a sheaf file or box of supporting documents. The acceptance was done by a NASA representative who was there all week just to verify and sign off every single box but in no particular order. Up to him what he worked on every day. One incorrigibly OCD nit-picking NASA Bureaucrat . he was. Perfect for aplex job where people lives were at stake so I did grant him that. Ok so once I got up to speed with all that came the real kicker; The Hard Part. That ten-page form had always traditionally beenpleted at thispany on a good OLD IBM Selectric writer. Yes they did have a decentpanyputer system that was not to be used by tradition. So every time the shipment auditor found a mistake or something to add the page it was on had to be redone by hand. Which happened all day long(!) italic Yeah for only one day. I went out on the Internet once I got home and found all of the different interactive .pdf versions of the form that were available for free and emailed a couple versions to my work PC. The next morning I filled the form with the info. I had so far which took about 3 minutes. Every time the guy came to hand me another page needing adjustment I took a five-minute break from my real job and made the update. My supervisor saw me doing this and made clear his suspicion that I was breaking some concrete process protocol. Oh Well italic Inevitably the auditor figured out that newly updated pages were no longering down the hall one at a time or at all and came to see what had interrupted my work. I showed him what I begun doing uncertain how he react. He just grinned put out his lower lip and said Finally you guys got the memo! No more crap to fix! italic Thank God I had some laziness left in me! Im pretty sure though that I didn make a friend of the guy I temporarily replaced; I done a real good job of permanently knocking over his rice bowl there. Oh Well italic
What is your review of Kindle Paperwhite (2015 version)?
I belong to the grandfather style of reading books and for the longest time I believed that physical books was the only way to read books- books and others. I used to scoff at anyone using ebooks to study and read. But modern technology has a way to creep into our lifestyles. I was already using my iPad to read PDFs of scientific papers books and academic articles. But I was actively avoiding a proper e-book reader with e-ink technology for the longest time. That changed this July when my wife gifted me my first kindle- Paperwhite (215 ed.). I've been using it for 3 months now and I'm now a total convert. These are few of my observations The letters are crisp and don't look like digital font. I like the new Bookerly font though I've not used any other font topare in detail. The pages look and feel is paper-like. I would say I don't miss paper at all except for the smell of fresh books. Unlike my iPad which I had to charge every 2-3 days the paperwhite I only have to charge about once in a month. I use my kindle for about 2-3 hours a day. The front-light tech is awesome because you can read with the lights off and there in no glare at all. It is easier to sleep after reading from the kindle. Staring at bright screens causes your biological clock to go haywire ( Bright Screens Could Delay Bedtime ). Shopping experience on Amazon store is excellent. You can buy in mobileputer or directly from the are cheaper than paperbacks and most titles are available in kindle format. Subscribe to some websites like and and you'll get many free eBooks and good deals in many others. From my experience the good and latest titles are difficult toe by in the deal section. Calibre is a useful free software to convert all non-kindle formats to mobi or azw formats. I only have to just email any document directly to my kindle id and WiFi will automatically update my kindle library. One hack I've learnt is that no matter what format the eBook is in when you email the book to your kindle id putting convert in the subject line will convert it to azw format. I've tried it with PDF and epub and this is very useful since this way I don't need to use Calibre. The number of non-academic books I have read has increased since I started using the kindle. I average about 3 books a month now. Pre- Kindle I was more like 1 book in 6 months. The Kindle app is very useful to have in my smart phone and it serves me one specific purpose which I'll detail in my next point. Initially I was having problems for my academic purposes. All scientific paperse in PDF format and not in the kindle format. The direct PDF page rendering is a chore. The enlarging and pinch function of the kindle is not very responsive. when you convert PDFs to kindle formats the graphstables and figures go haywire. I have found a simple way around this PDF is converted to azw format the is more or less spared from any formatting if it is not a scanned copy. So I email the PDF file twice to my kindle id- once with convert in subject line and one without. So now I have both PDF and azw format in my kindle. There is simultaneous syncing of all files in the cloud. I read the from my kindle and look up the graphs and tables in my cell phone. This way I can spare the battery of my cell phone from draining out while I'm reading. I'm especially proud of this hack. In summary I'm a very happy customer. My reading has increased-both academic as well as non-academic (fiction and non-fiction). I'm hardly using my iPad now and the kindle along with some support from my cell phone is doing the job for me. Generally over the last several years my online consumption of information has greatly increasedpared to printed material like newspapers magazines books journals etc. This has been possible because of the many news & magazine apps digitization of even academic content and improvement of internet. So now it is cheaper convenient and more efficient to access information. I think embracing new technology like the Kindle is the way to go! PS I do hope Amazon is working on making PDFs a little better reading experience than it is now.
What's the easiest way to import a PDF table into Excel?
You get great results in two shakes of a lamb tail by using Word as an intermediary when copying data from a pdf table into Excel. A pdf file contains hints about how the table should be displayed which are copied to the clipboard and recognized by Word (but Excel does not). That why copying directly from a pdf into Excel fails but pasting into Word succeeds. When you copy from a Word table to the clipboard Word adds its own hints about how to display the dataand Excel recognizes those. Assuming that your pdf table is not a scanned image the simplest and surprisingly effective procedure is Copy the table from the pdf Copy the resulting Word table If the table is scanned in the original pdf I suggest opening the pdf file in Word. Word 213 has a pdf to Word conversion feature and Office has an Optical Character Recognition (OCR) feature. Between the two of them you just might be able to get an editable Word table out of the data. Once you do you can copy it and paste in Excel.
Why does Excel have different units for row height and column width?
The column appears to be the number of standard-size characters that will fit in the cell using the font and font size specified by the Normal style. If a monospaced font (like Courier) is used in the Normal style all characters are the same sizeso the column is exactly how many will fit. In a proportional-spaced font (like Calibri) the numbers through 9 are monospaced and can be used to confirm the column . To a good approximation a capital X is also a standard-size character. You may test my assertion by typing numbers in a cell until you can fit another one. Then look at the HomeFormatColumn property. Next rightclick the Normal style in the Home ribbon and choose Modify. Change the font size to something outlandish like 36 point. The column will be relatively unchanged but the resulting columns are a whole lot wider than they used to be. You may wonder why Excel frequently overrides your choice of column such as by changing 8.5 to . Microsoft looks at the number of pixels that correspond to your choice of column and rounds your choice so that it converts into a whole number of pixels. Microsoft discusses the column issue in s s Row is always measured in points however. So when you change the font size in the Normal style the row changes along with it. As to why Microsoft did it this way I don know. But it wouldn surprise me if they were just trying to do the same thing that Lotus 123 did. And that Lotus 123 did it that way because it was important for a non-GUI program to know roughly how many characters would fit in a cell but they let the typographers have their points for row .