Excel Help Thread 📊

Still generally a lot easier in most companies to get Access installed than SQL Server. The interface is a lot kinder to newcomers and, if you design it right, you can eventually just swap out your access backend with SQL Server linked tables with basically no impact on the FE.

1 Like

This piqued my interest a bit. I wondered what might cause that, because my inclination is that Excel is generally pretty neutral about doing a lot of linear calculations either within one cell or across several (ie where each split-out cell formula only references its parent cell). But I don’t know. The big bugbears of slow spreadsheets though are volatile functions, conditional formatting and vlookups. Cut all those out and you usually have a fast spreadsheet whatever you do.

Yeah I was a big Access user back in the late 1990s. Haven’t used it since. I think honestly that I’d argue that if SQL Server is too much of a forward push I’d take advantage of the fact that Excel’s developed so much over the last couple of decades that it’s now almost a viable substitute for simulating a very basic database. Not a lot that Access can do that Excel can’t although I’d admit that at least Access semi-forces you to abide by good database practice (lol, no it doesn’t :slight_smile: )

Yeah it’s highly limited but it has a niche and the sort of things discussed here are it, really.

You can install SQL Server but you can’t just drag an Excel sheet into a DB and have it be treated like a table AFAIK (for example).

Legacy or otherwise it is also still an offered Office package

Yeah but it’s not a smart career move to make it your speciality is it.

You can just about do this using the import wizards, if you don’t mind all your data getting mangled :slight_smile:

1 Like

Thinking of which

Wizards.

  • Very 2000s thing
  • Still a very valuable and useful tool for basic tasks
  • I blame JK Rowling

0 voters

I’m not sure they are tbh. I think it’s more a case of person who is used to using databases to solve problems suggests solving problems using a database! I have a similar relationship with VBA: no matter how much easier or faster it might be do something ad hoc in another language or platform, my first instinct when trying to get something working quickly is to hit ALT + F11 in Excel and get straight into it!

You’re right, I probably should have alluded to th-

:wink:

Yeah I was being glib and should have qualified with some.

Eg Access allows the use of SQL like statements to do a contains thing which was discussed earlier as being a much more complex and counter-intuitive thing in Excel

1 Like

I can’t be expected to read all of your posts mate. There’s only so much solid gold a man can healthily ingest in one day.

[Considered keeping my initial “golf” typo there]

1 Like

TBH I’ve always felt Access is a bit hard done by. The main cause is probably just how expensive MS always made it. I could understand if they actually deprecated it but they clearly haven’t.

If it was just in the normal office suite beside Word and Excel I’d imagine it would have found a big niche with charities and small businesses.

But here we are…

Decided to have a look on YouTube to see some basic tutorials on how to do some SQL stuff.

Good to see at least one of the guys from Freaks and Geeks is doing well

1 Like

SQL is very straightforward which is nice. Also it’s mostly the same whether you’re using MS, MySQL or whatever.

1 Like

If I have a column that contains both first names and surnames is there an easy way to split this into two columns without needing to do it manually?

https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

Do you have a comma between first and second name?

1 Like

I’ve got a whole Moodle unit on databases/SQL that you can do if you want x

1 Like

Perfect, thank you! No comma but I think I can select ‘space’ and that should work. Or I can add commas if need be.

Only problem with space might be if you have like 2 first names that aren’t hyphened, like Mary Jo or something and surnameslile Van Something. But should be easy to pick up.

1 Like

seems like text to columns is doing the trick, but if you want another method (say there are two spaces and you dont want three columns), you could do a formula that searches for the space, off the top of my head it would be =search(" “,A1) which would give you the number that the space appears, so then if you wanted the first name you could try something like =left(A1,search(” “,A1)-1) the left formula references a cell and then takes whatever number of characters you specify, so the previous formula minus 1 gives you the right number for the first name, you could use a similar formula to get the last name by using mid instead of left, it would be something like =mid(A1,search(” ",a1)+1,1000) the three arguments for mid are cell to evaluate, the first character to start on (which would be the search for the blank space plus one) then how many characters to take (so i’ve used 1000 which should be enough)

haven’t tested these so might not work